 Home » Expert » Convert Number to Words in Excel

# Convert Number to Words in Excel

Last Updated on 12/05/2022 by Frédéric LE GUEN

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

1. Istiak Ahmed
07/01/2023 @ 18:56

Thanks for the formula you've given here. It's very helpful.
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)
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.

2. Bharat
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

3. Ahmed Elhawary
10/10/2022 @ 16:08

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

• Frédéric LE GUEN
18/10/2022 @ 16:21

Yes, directly inside the formula

4. DanlB
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?

• Frédéric LE GUEN
12/05/2022 @ 18:23

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

• danlb
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"

• Frédéric LE GUEN
12/05/2022 @ 19:37

• DanlB
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 ", "" ),

5. Roopesh V Madhavan
03/05/2021 @ 08:57

Very interesting

# Convert Number to Words in Excel

Last Updated on 12/05/2022 by Frédéric LE GUEN

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

1. Istiak Ahmed
07/01/2023 @ 18:56

Thanks for the formula you've given here. It's very helpful.
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)
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.

2. Bharat
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

3. Ahmed Elhawary
10/10/2022 @ 16:08

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

• Frédéric LE GUEN
18/10/2022 @ 16:21

Yes, directly inside the formula

4. DanlB
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?

• Frédéric LE GUEN
12/05/2022 @ 18:23

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

• danlb
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"

• Frédéric LE GUEN
12/05/2022 @ 19:37

• DanlB
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 ", "" ),

5. Roopesh V Madhavan
03/05/2021 @ 08:57

Very interesting