Convert a number to words

To convert a number to words, the solution was always to create a very complex VBA macro. Some websites, like this one , give you an example of VBA code to do the job. But, if you aren't familiar with VBA, it could be difficult to integrate this solution in your workbook.

Now with Excel 365 and the new LET function, you can convert a number to words. The formula is very complex and took a long time to develop. But despite this, there are certain points of the formula that must be analyzed before it can be used.

Characteristic of the formula

Before copying the complete formula and applying it to your workbook, it is important to analyze some points of the formula to avoid mistakes.

Principle of arrays in Excel

Excel 365 is the only version that can understand dynamic array (i.e. the result is returned in many cells).

An array ​​is always written between brackets. But in function of the settings of your computer, there could be a difference for the separator.

For example, for a US setting,

• the separator for the rows is the semicolon (;)
• the separator for the columns is the comma (,)

Whatever your local settings, the row separator is always the semi-colon. But for the column separator, there is difference in function of your local settings.

• in France, the column separator is the period (.)
• in Spain, the separator in the backslash (\).

Check this setting on your computer before to use this formula

Decimal separator

One of the trick of this formula is to detect the decimal numbers. Decimal number or not, the formula manage the 2 situations.

The trick lies in this part of the formula.

N, SUBSTITUTE(TEXT(A1, REPT(0,9)&".00" ),".","0")

Without going too deep in the detail of this formula, the decimal separator is mentioned 2 times in this part of the formula

• .00
• and the replacement symbol of the SUBSTITUTE function at the end ".","0".

If you are working with the decimal comma (,), you must replace these 2 periods by commas in the function

N, SUBSTITUTE(TEXT(A1,REPT(0,9)&",00" ),",","0")

Dollars / Cents or nothing

The formula will always added Dollars after the units and Cents in case of decimals.

Denom, {"million", "thousand", "Dollars", "Cents"}

Now, if you do not wish to display the words Dollars and Cents, you simply have to remove these words BUT YOU MUST KEEP the empty quotes to respect the number of occurrences in the matrix.

Denom, {"million", "thousand", "",""}

Formula to convert a number to words

Here is the full formula. If it's not working, change the parameters as it is explain before.

=LET(
Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},
Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
Tens, {"","Ten","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},
grp, {0;1;2;3},
LET(
N, SUBSTITUTE( TEXT( A1, REPT(0,9)&".00" ),".","0"),
H, VALUE( MID( N, 3*grp+1, 1) ), T, VALUE( MID( N, 3*grp+2, 1) ),
U, VALUE( MID( N, 3*grp+3, 1) ),
Htxt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),
Ttxt, IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
Utxt, IF( (T+U), IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
CONCAT( IF( H+T+U, Htxt & Ttxt & Utxt & Denom, "" ) )
)
)

This formula has been created by Peter Bartholomew 👏👍