Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Numbers to Words (money)

    Microsoft has a macro to convert a number to the words (e.g., 25 becomes Twenty Five Dollars and No Cents).

    The problem w/their macro is that it is grammatically incorrect (at least in the US).

    25 should be "twenty-five" with the hyphen.

    Also, something like 12345 gets converted to: Twelve Thousand Three Hundred Forty Five Dollars and No Cents

    Not only should the Forty-Five have a hyphen, but also there should be a comma after the "Thousand" and either "and" or an ampersand before "Forty"

    It would also be nice to have the macro write the number as on a check when there are cents.
    It would typically show "25/100" for 25 cents rather than spelling out Twenty-Five Cents.

    Does any know of another macro that does it correctly?
    Last edited by kweaver; 2016-06-01 at 19:20.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,632
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Is this something you can achieve with AutoCorrect Options?
    File > Options > Proofing > AutoCorrect Options... (2010)

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    While I could enter all of the possibles (should be easier) like "Twenty Five" to "Twenty-Five" that only seems work when I type the entry in words.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,632
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Seems to work for me although I would never want to substitute Twenty-Five for 25

    KW1.png

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Ah, you're right regarding the numbers like that but, as you said, wouldn't want all 25s to be changed.
    But, it won't handle the various cases like 12345 to be Twelve Thousand, Three Hundred and Forty-Five.
    I'd have to enter every number. YIKES.

    The MS macro "almost" works -- just grammatically incorrect.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,632
    Thanks
    115
    Thanked 645 Times in 589 Posts
    KW,

    Can you post the link to the MS macro. Perhaps it can be tweaked.

    Maud

  7. #7
    New Lounger
    Join Date
    Feb 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud,

    Here is one version:
    https://support.microsoft.com/en-us/kb/213360

    --
    Regards,
    Tom Ogilvy

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks, Tom. That's the one I've been using. I wrote some regular code in 2 other columns to adjust for what I wanted.
    Not good enough to tweak the macro on my own, so thought someone may have done it in the past. Maud will score, I'm sure.

  9. #9
    New Lounger
    Join Date
    Jun 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Several years ago there was a data base program called "ENABLE". Using that I programed to convert numbers to words for check writing. It followed all the grammatical rules described except for the comma after "Thousand" Microsoft bought the Company and discontinued the data base.

  10. #10
    Lounger
    Join Date
    Jan 2011
    Posts
    36
    Thanks
    4
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by kweaver View Post
    Microsoft has a macro to convert a number to the words (e.g., 25 becomes Twenty Five Dollars and No Cents).

    The problem w/their macro is that it is grammatically incorrect (at least in the US).

    25 should be "twenty-five" with the hyphen.

    Also, something like 12345 gets converted to: Twelve Thousand Three Hundred Forty Five Dollars and No Cents

    Not only should the Forty-Five have a hyphen, but also there should be a comma after the "Thousand" and either "and" or an ampersand before "Forty"

    It would also be nice to have the macro write the number as on a check when there are cents.
    It would typically show "25/100" for 25 cents rather than spelling out Twenty-Five Cents.

    Does any know of another macro that does it correctly?
    Hi KW,

    If it is for a check writer, I am attaching an Excel file that I used some years ago (no macros - just a lot of VLOOKUP). I have tweaked it slightly to your requirements (hopefully).

    If you would like the original file, do let me know.

    Please note that I am unsure if the file is under copyright as I had got it of the web some years ago. My apologies to the holder of the copyright if I have caused any infringement.

    Mike
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    MikeDee...thanks. It's actually for a "fake" check but looks like a real one. It's done via a Mail Merge.

    Your version, unless I'm mis-reading, doesn't put in the hyphen consistently.
    Last edited by kweaver; 2016-06-02 at 12:36.

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    I have most of it done as you want, but how do you want these:
    123456.287
    e.g. "One hundred and twenty-three thousand, four hundred and fifty-six dollars and 28/100"
    12026.4
    e.g. "twelve thousand and twenty-six dollars and 40/100"
    e.g. "twelve thousand no hundreds and twenty-six dollars 40/100"

    zeddy

  13. #13
    Lounger
    Join Date
    Jan 2011
    Posts
    36
    Thanks
    4
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    I have most of it done as you want, but how do you want these:
    123456.287
    e.g. "One hundred and twenty-three thousand, four hundred and fifty-six dollars and 28/100"
    12026.4
    e.g. "twelve thousand and twenty-six dollars and 40/100"
    e.g. "twelve thousand no hundreds and twenty-six dollars 40/100"

    zeddy
    Hi zeddy,

    Firstly please keep in mind that this is not my workbook and that I have just been experimenting with the various layouts - not having used it for 10 years.

    I'm not really an expert in this - but for :

    123456.287 - you will need to do some digging into the formulae if you want the 3rd decimal place. If however, you actually meant 123456.28

    "One hundred and twenty-three thousand, four hundred and fifty-six dollars and 28/100" - replace "Cents" in cell G2 with "/ 100" AND replace "=VLOOKUP(D7,Words,2,FALSE)" in cell F7 with "=VLOOKUP(D7,Words2,2,FALSE)"

    "twelve thousand and twenty-six dollars and 40/100" - replace "Cents" in cell G2 with "/ 100" AND remove the "," in cells A7, A15 and cells A19 AND replace "=F19&F18""with "=F19&F2&" "&F18"

    It would take me a while to sort out the third one - but what I would suggest is that you go through and play about with the formulae in "F2W Code" and experiment.

    It might also be a good idea for you to save the workbook with different filenames - depending on what outcome you need. Your mileage may vary depending on the numbers that you use.

    IMPORTANT - be aware that a lot of the cells have blank spaces either before or after the contents - and changing / removing the spaces will alter the output

    HTH

    Mike

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thanks, Zeddy.

    123456.287 would be One Hundred and Twenty-Three Thousand, Four Hundred and Fifty-Six Dollars and 29/100 (if you want to throw in rounding to be thorough. In my case, there's no rounding...all of the money in the "pennies" is 2-digits).

    12026.40 would be Twelve Thousand, Twenty-Six Dollars and 40/100

    Are you writing a macro from scratch or modifying the Microsoft one?

    Kevin

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Kevin

    ..I'm starting from scratch.
    My example of 123456.287 was to test whether you wanted to ignore or use rounding for the cents.
    So what about what numeric range shall we work to?
    I would think that, for cheques, oops, for checks, perhaps numbers LESS than a billion would be OK???

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •