Looking to convert numbers to words in Excel without using complex VBA? You're in the right place if you need to convert number words!
Convert Numbers to Words in Excel
This guide will show you a powerful new way to use Excel 365's LET function. Say goodbye to difficult VBA scripts and hello to a formula that makes it easy to convert number words.
With Excel 365, you can convert numbers to words using a dynamic formula. While intricate, I’ve broken down the most critical aspects for you to understand and apply smoothly.
Key Formula Characteristics
Before using the full formula, consider these key characteristics to ensure you avoid errors in your workbook.
Understanding Array Functions
Excel 365 is essential for understanding dynamic arrays, where results spill across multiple cells. Arrays are enclosed in brackets, and the separator varies based on your computer's locale settings.
For example, with US settings:
- Row separator: semicolon
( ; ) - Column separator: comma
( , )
However, the column separator differs in some locales:
- In France, it's a period
( . ) - In Spain, it's a backslash
( \ )
Tip: Check your local settings before applying the formula to ensure correct separators.
Handling Decimal Separators
This formula handles both whole and decimal numbers. The secret lies in substituting the decimal separator based on your locale. Here's the critical part of the formula, which is vital when you want to convert number words accurately:
N, SUBSTITUTE(TEXT(A1, REPT(0,9)&".00"), ".", "0")
If you use a decimal comma, update the formula like this:
N, SUBSTITUTE(TEXT(A1, REPT(0,9)&",00"), ",", "0")
Dollars, Cents, or No Labels
By default, the formula appends “Dollars” and “Cents” to numbers. If you prefer no labels, remove the text but keep the empty quotes when you need to convert number words without extra text.
Denom, {"million", "thousand", "", ""}
The Formula to Convert Numbers to Words
Below is the full formula. If it doesn’t work, adjust the settings as explained earlier to properly convert number words.
=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 incredible formula was crafted 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