Convert number to written words

[welchs101]

Hi,

I am working on a vba program and one of the things i want to do is convert say a number like
98261.15

to written word like this

Ninety Eight thousand two Hundred Sixty One and 15 cents

Does anyone know how to do this?

[Logit]

.
This is one method :

Please Login or Register  to view this content.

[welchs101]

Hi , how does the number to words work.....when i try to run this no macro shows up to run.

[welchs101]

oh i see now....both are "functions" that one can create in excel...........i see thanks...i will take a look

[Rick Rothstein]

Here is another function that I posted in my mini-blog article here that you can consider (one of its modes outputs dollars and cents, but if you need the "dollars" removed, I can do that for you)...

Yet Another Number-To-Words Function (Sorry, US Style Only)

Note: The function at the link above is capable of handling an absolutely huge number (passed into it as a text string), much larger than almost anyone would ever need (a fraction less than a quintillion... that is a number with 18 digits in front of the decimal point!).

[welchs101]

question: about the declaration in the spellnum function

Please Login or Register  to view this content.

if i wanted to "remove" the user defined function and use this code to convert to words in my actual code.........

what is the "ByVal MyNumber" declaration.........is it a long variable or something else?

[Rick Rothstein]

if i wanted to "remove" the user defined function and use this code to convert to words in my actual code.........

Why? You gain absolutely nothing by doing that and probably cause all kind of problems by trying to integrate this particular function into your existing code. Simply place the function code into a module and when you need to do the conversion, call the function and let it do its thing.

While I am guessing you are happy with the function previously posted, I just wanted to point out the message I posted in Message #5 (you may have missed it as you were posting your last message at almost the same time I posted mine).

[welchs101]

ok.... so if i want to use it in my code just call the function like you said...got it.

also, you mentioned that the value i pass is a "text string" and 'NOT' a number....is this right?

[welchs101]

One more question.....is there a way to pass it the varibles like instead of "Dollar" replace with "Euro"?

[Rick Rothstein]

also, you mentioned that the value i pass is a "text string" and 'NOT' a number....is this right?

If the number is not converted to exponential notation (less than 11 digits on a worksheet, less than 16 digits within the VBA world), you can pass the number into the function as a number or text string, otherwise it must be passed as a text string (to avoid having Excel or VBA convert it exponential notation before the code can see all the digits).

One more question.....is there a way to pass it the varibles like instead of "Dollar" replace with "Euro"?

I could modify the program to do that if you wish, but you could also simply pass the output from my function into VBA's Replace function or Excel's SUBSTITUTE function (depending on where you are calling my functon from) and do the text replacement that way.

[welchs101]

as far as updating the program i think i will do as you suggested and just do a replace type thing. I think that will work fine.

regarding the "exponential" thing you mentioned................ah i am not sure what you're talking about. i will look up exponential notation for excel now.

[Metoo7]

https://www.ablebits.com/office-addi...s-words-excel/

[welchs101]

so when i think of exponential notation i think of scientific notation.........and i dont think this is what you were talking about...right?

[Rick Rothstein]

so when i think of exponential notation i think of scientific notation.........and i dont think this is what you were talking about...right?

Same idea. To see what I mean, type 12 digits into a cell in Excel and then hit the Enter Key... note your original 12 digits have been converted to exponential format (same thing happens in VBA, you just get more specify more digits before that happens). Unfortunately, if you do not make your long numbers text, the exponential numbers is what my function recieves.

[welchs101]

i can get it to work for up to 15 characters....any more than that and it does not work. I did not see the exponential thing you mentioned.

[welchs101]

i see what you're saying now.....i went to one of the other files you sent me and when i typed in 13 digit number it converted it to scientific notation and the program does not work with such a thing.

is there a way to get the program to work with regardless with the number displayed?

[welchs101]

One thing i did figure out is this. If you format the cell that the number is in as a "Number" instead of "General" then the scientific notation does not seem to occur.

But i wish i did not even have to do this.

[Rick Rothstein]

...and when i typed in 13 digit number it converted it to scientific notation and the program does not work with such a thing.

is there a way to get the program to work with regardless with the number displayed?

The Number format may not be the answer (depending on how many digits you number has) as Excel will covert any digits after the 15th digit to zeros. Formatting the cell as Text allows the function to see all of the digits and process them.

[welchs101]

Not sure if this applies to this thread or if i need to start another but i will post here and see where it goes.

Convert number to written words for ENGLISH works good enough.
Does anyone know of the same type of thing for Russian?

I am making something where i need both English and Russian written word to describe a numeric value.

Any one know of a Russian version of this?

[Rick Rothstein]

I do not know how to read or speak Russian, so I cannot help. My guess is that you would be better served starting a new thread for a Russian number to text function as any one who might know how to write such code is probably not reading this thread any more (if they even did so initially), so a new thread might grab their attention.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.