How to convert a numericaldigit to words?Thanks inadvance.

[Ron Coderre]

There's a NumsToWords() user defined function (UDF) file you can
download at this website:
http://www.contextures.com/excelfilesRon.html

The NumsToWords() function allows you to set the major currency (eg
Dollars),
the minor currency (eg Cents) and the word that connects the major and minor
currencies (eg And).

The file is completely unprotected, so you have full access to all of the
VBA code. The cover sheet in that file has instructions, several usage
examples AND a button that will export the NumsToWords program into any open
workbook you choose.

There's also a NON-VBA NumsToWords approach at that website.

Other sites that may be of interest:
http://support.microsoft.com/default.aspx?scid=kb;en-us;213360
http://www.ozgrid.com/VBA/ValueToWords.htm
http://xldynamic.com/source/xld.xlFAQ0004.html

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Bernd P]

Hello Ron,

XLDynamics is currently not available, but see the following test
results:

http://www.sulprobil.com/html/spellnumber.html
1E+15 >>>>> Error (Absolute amount > 999999999999999)! <<<<<
0,123 Zero Dollars and Twelve Cents (rounded)
-1 Minus One Dollar and Zero Cents
20,123 Twenty Dollars and Twelve Cents (rounded)
-20,123 Minus Twenty Dollars and Twelve Cents (rounded)
1,01 One Dollar and One Cent
1000001,01 One Million One Dollars and One Cent

http://www.contextures.com/excelfilesRon.html (VBA)
1E+15 #ZAHL!
0,123 No Dollars and No Cents
-1 #WERT!
20,123 Twenty Dollars and No Cents
-20,123 #WERT!
1,01 One Dollar and No Cents
1000001,01 One Million One Dollars and No Cents

http://support.microsoft.com/default.aspx?scid=kb;en-us;213360
1E+15 Hundred Fifteen Dollars and No Cents
0,123 No Dollars and Twelve Cents
-1 One Dollar and No Cents
20,123 Twenty Dollars and Twelve Cents
-20,123 Hundred Twenty Dollars and Twelve Cents
1,01 One Dollar and One Cent
1000001,01 One Million One Dollars and One Cent

http://www.ozgrid.com/VBA/ValueToWords.htm
1E+15 Hundred Fifteen Dollars and No Cents
0,123 No Dollars and Twelve Cents
-1 One Dollar and No Cents
20,123 Twenty Dollars and Twelve Cents
-20,123 Hundred Twenty Dollars and Twelve Cents
1,01 One Dollar and One Cent
1000001,01 One Million One Dollars and One Cent

Please note that in German Excel the "," represents the "." but you
should get similar results.
IMHO any serious spellnumber function should check its inputs
thoroughly and all your suggested versions DON'T (to be fair to
XLDynamics, I did not check recently, so that's maybe an outdated
status of information).

Regards,
Bernd

 

[Ron Coderre]

Thanks for pointing out the non-US region issue in the
http://www.contextures.com/excelfilesRon.html (VBA) file.
That is now fixed.

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Bernd P]

Hello Ron,

Sorry if I appear meticulous but your VBA version still returns a
positive string expression for negative numbers, it returns an Excel
error value (and not a string error message) for values which cannot
be represented (too high, for example) and it rounds to cents without
informing the caller.

I mention this because this program is quite often used to print out
cheques. An Excel error value might be tolerable here but swallowing
minuses is certainly not and rounding to cents is at least dangerous
because its possibly unintended (so its not warning about a possible
error).

Regards,
Bernd

 

[Ron Coderre]

You are certainly entitled to your opinion.

To my mind:
1) The NumsToWords function only translates
the input into words. It does not claim to be
valid for all uses.

2) Any check printing program that would allow
invalid amounts to process is a problem much
larger than that UDF.

3) The UDF returns these values for unusual inputs:
1E+15 #NUM!
....The number is invalid in general and especially for currency.
....Excel would round values greater than 15 places.
....The error is legitimate.
....See comment 2)

0.123 No Dollars and Twelve Cents
20.123 Twenty Dollars and Twelve Cents
-1 One Dollars and No Cents
-20.123 Twenty Dollars and Twelve Cents
....Negative signs are ignored.
....Yes, it rounds.
....See comment 2)

The Microsoft code returns these values:
1E+15 Hundred Fifteen Dollars and No Cents
....I'd rather see a #NUM! error

0.123 No Dollars and Twelve Cents
-1 One Dollar and No Cents
20.123 Twenty Dollars and Twelve Cents
....Negative signs are ignored.
....it rounds.
....See comment 2)

-20.123 Hundred Twenty Dollars and Twelve Cents
....Invalid description. There are no hundreds.
....See comment 2)

Other points to consider.
- If the user is aware that rounding is
occurring and wishes checks to be printed
with the rounded amounts...Do they want
the check to read this way?:
"Twenty Dollars and Twelve Cents (rounded)"

- If negative amounts are to be
refunded...Do they want the check to read
this way?:
"Minus Twenty Dollars and Twelve Cents"

Not surprisingly, one size rarely does fit ALL.
Fortunately, there are several options available to them.

Best Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Bernd P]

Hello,

...
IMHO any serious spellnumber function should check its inputs
thoroughly and all your suggested versions DON'T (to be fair to
XLDynamics, I did not check recently, so that's maybe an outdated
status of information).
...

XLDynamics is alive again. Bernie's UDF cited on
http://xldynamic.com/source/xld.xlFAQ0004.html results in:
1.00E+15 One Dollars Only
0.123 Dollars And 12
-1 #VALUE!
20.123 Twenty Dollars And 12
-20.123 #VALUE!
1.01 One Dollars And 01
1000001.01 One Million One Dollars And 01

Regards,
Bernd