Results 1 to 7 of 7

Thread: VBA convert numbers to letters

  1. #1

    VBA convert numbers to letters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all, I'm a real newbie with the VBA code and I need one to convert numbers to letters. I found one but I need to modify it to insert a "-" in the result. The result with the current VBA is 21 = twenty one. And what I need is 21 = twenty-one. The code is attached. Thanks for your help!!
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    In the function try changing the "Twenty " , etc. which have a space on the end to "Twenty-"

  3. #3
    I've tried this but when the numbers ends by 20 such as 220, it will result in two hundred twenty- . Is there a way to only add the "-" when it's not the ending number?

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    In that case then I'd look into the "Replace" function to work on the result and replace "- " with " " , that's hyphen space with single space.

    http://www.techonthenet.com/excel/fo...eplace_vba.php

  5. #5
    Thanks for your help NoS. I've tried to insert the replace function but I keep getting error messages. How should I type the code? I tried after the "GetTens = Result" line and a couple of other places, but it's not correct. Damned legal department who wants this hyphen.... thank you really appreciated

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Sorry gcaron, not ignoring you, 'bin away all day.

    By using these functions, you are ending up with a "string variable" of words that represents the number. Call that variable "NumberWords" and this is what you are inserting into your code. It's the variable "NumberWords" that needs tuned up to eliminate the hyphen on the end.

    If NumberWords equals "two hundred twenty- " , after this

    NumberWords = replace(NumberWords, "- ", " ")

    NumberWords will equal "two hundred twenty " , with just a space on the end.

    Now use NumberWords in your code and it should be right. ..... but there could be other situations that need adjustment too, like if twenty- was at the end of a sentence followed by a period not a space. The above would miss it but this should just remove the hyphen

    NumberWords = replace(NumberWords, "-.",".")

    You could put these two lines one after the other with no issues.

    Hope that is of some assistance.
    NoS

  7. #7
    Thank you NoS. It works fine now. Thanks for your help!!!

Posting Permissions

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