Convert Number to Words in Excel

Last Updated on 12/05/2022
Reading time: 2 minutes

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 (,)
Array column separator 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 (\).
Array column separator 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 👏👍

10 Comments

  1. Istiak Ahmed
    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.

    Reply

  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

    Reply

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

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

    Reply

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

      Yes, directly inside the formula

      Reply

  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?

    Reply

    • 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)

      Reply

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

      Reply

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

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

      • 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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Convert Number to Words in Excel

Reading time: 2 minutes
Last Updated on 12/05/2022

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 (,)
Array column separator 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 (\).
Array column separator 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 👏👍

10 Comments

  1. Istiak Ahmed
    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.

    Reply

  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

    Reply

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

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

    Reply

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

      Yes, directly inside the formula

      Reply

  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?

    Reply

    • 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)

      Reply

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

      Reply

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

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

      • 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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *