VBA convert numbers to letters

gcaron

New member
Joined
Oct 17, 2013
Messages
4
Reaction score
0
Points
0
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!!
 

Attachments

  • VBA - number letter.docx
    13.8 KB · Views: 23

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
In the function try changing the "Twenty " , etc. which have a space on the end to "Twenty-"
 

gcaron

New member
Joined
Oct 17, 2013
Messages
4
Reaction score
0
Points
0
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?
 

gcaron

New member
Joined
Oct 17, 2013
Messages
4
Reaction score
0
Points
0
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

gcaron

New member
Joined
Oct 17, 2013
Messages
4
Reaction score
0
Points
0
Thank you NoS. It works fine now. Thanks for your help!!!
 
Top