## 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 👏👍

07/01/2023 @ 18:56

Thanks for the formula you've given here. It's very helpful.

Could you please help me by editing in my currency like below format -

1 = One Taka Only

10 = Ten Taka Only

100 = One Hundred Taka Only

1000 = One Thousand Taka Only

1000.50 = One Thousand Taka & Fifty Paisa Only (Here after decimal we called it Paisa instead of Cent)

Also add Lac instead of millions like

100000 = One Lac Taka Only

1000000 = Ten Lacs Taka Only

10000000 = One Crore Taka Only

It will help me a lot if you can edit this in this above format.

Thanks in advance.

29/11/2022 @ 18:16

Hi,

Thanks for the formula.

but I didn't get actually where we have to insert the number

^Bharat from india

10/10/2022 @ 16:08

Can i change the currency? and thank you so much for this formula!

18/10/2022 @ 16:21

Yes, directly inside the formula

12/05/2022 @ 17:23

I copied the above formula and put it in Excel and it mostly works. A copy of the input and out put is as follows

10 Ten Dollars

1000 One Thousand

3545.25 Three Thousand Five Hundred Forty-Five Dollars Twenty-Five Cents

3500020 Three Million, Five Hundred FALSE Thousand Twenty Dollars

100 One Hundred FALSE Dollars

Why is "Dollars" left off the "One thousand" and why is the word "FALSE" appearing?

12/05/2022 @ 18:23

I will check that (and I will update the formula if I found how to correct it)

12/05/2022 @ 19:00

The line

Utxt, IF( (T+U), IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),

Should be

Utxt, IF( (T+U), IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ),"" ),

To get rid of the "FALSE"

12/05/2022 @ 19:37

Not bad, I have already updated the formula but your writing is shorter

14/05/2022 @ 21:00

If it is an even Thousand (1, 2, 3, ... 99) or million (1, 2, 3, ... 99) without a hundreds, tens, or ones digits, Dollars are omitted. I think this is because the line

Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},

has those words with the denomination words. I have not yet worked out the code but I think that Denom should only be Dollars " & " Cents" and Thousands and Millions handled like Hundreds are in the line

Htxt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),

03/05/2021 @ 08:57

Very interesting