Convert a number to words in Excel using this amazing function. For example, 123,456 to One hundred twenty-three thousand four hundred fifty-six. You can use this elegant Excel formula to convert number to words.
Excel Number to Words Formula
Below I have provided number to words Excel formula. It assumes you have input number in cell A1.
Note: This function can convert numbers up to 999,999 into words.
The function:
=LAMBDA(number,
LET(
th, INT(number / 1000),
th_h, MOD(th / 100, 10),
th_t, MOD(th, 100),
th_tens1, INT(th.t / 10),
th_tens2, MOD(th.t, 10),
h, MOD(D2 / 100, 10),
t, MOD(D2, 100),
tens1, INT(t / 10),
tens2, MOD(t, 10),
tens, {"twenty", "thirty", "fourty", "fifty", "sixty", "seventy", "eighty", "ninety"},
upto19, {"one", "two", "three", "four","five", "six", "seven", "eight", "nine", "ten"," eleven", "twelve","thirteen", "fourteen", "fifteen","sixteen", "seventeen", "eighteen","nineteen"},
CONCAT(
IF(
th < 1,
"",
IF(th_h >= 1, INDEX(upto19, th_h) & " hundred ", "") &
IF(
th_t < 1,
"",
IF(
th_tens1 < 2,
INDEX(upto19, th_t),
INDEX(tens, th_tens1 - 1) &
IF(th_tens2 >= 1, "-" & INDEX(upto19, th_tens2), "")
)
) & " thousand "
),
IF(h >= 1, INDEX(upto19, h) & " hundred ", ""),
IF(
t < 1,
"",
IF(
tens1 < 2,
INDEX(upto19, t),
INDEX(tens, tens1 - 1) & IF(tens2 >= 1, "-" & INDEX(upto19, tens2), "")
)
)
)
)
)
How this formula works?
Formula & UDF to get Words from Number
Click here to download the sample file for this page. You can see number to words formula in column C. Play with the values or examine the formula to learn more.
In order to understand the number to words formula, you must first understand the newly introduced LAMBDA() & LET() functions.
LAMBDA Excel Function:
We can use LAMBDA() function to create custom functions in Excel easily. For example, we can take the logic of converting number to words and wrap it a LAMBDA with below syntax and call it number2words() as a custom function.
LAMBDA Syntax:
=LAMBDA(value, logic_to_convert_the_value_to_result)
Learn more about LAMBDA function in Excel here.
LET Excel Function:
LET function let’s us define variables to use with in the context of a formula. You can use LET function to shrink long formulas. Here is a quick example to explain the LET function.
Original formula:
=IF(SUM(A1:A10)>100, “Too high”,
IF(SUM(A1:A10)>20, “Medium”,
IF(SUM(A1:A10)>0, “Positive”,
“Could be zero or negative”)))
Same formula with LET():
=LET(s, SUM(A1:A10),
IF(s>100, “Too high”,
IF(s>20, “Medium”,
IF(s>0, “Positive”,
"Could be zero or negative”))))
We are using the SUM(A1:A10) several times in the original formula. In the LET() formula version, we start by defining a variable s that is equal to SUM(A1:A10) and then we use s in rest of the formula. This simplifies the formula and supposedly makes it faster too (as Excel would calculate SUM(A1:A10) once.
Learn more about LET function
LET function is introduced newly and available only in Excel 365. Click here to read the documentation on LET function.
Understanding Number to Words Excel formula
The process for turning number to words is not complicated. If you know how to convert words for numbers up to 999, then same logic is applied to thousands, millions and billions too.
So let’s understand the process for numbers up to 999.
- Define two arrays upto19 and tys to hold {one,two…,nineteen} and {twenty, thirty…,ninety} respectively.
- From the input number (say in A1), calculate these 4 numbers and store them in variables.
- h = MOD(A1/100,10)
- t = MOD(A1,100)
- tens1 = INT(t/10)
- tens2 = MOD(t,10)
- One way to look at these four variables is,
- h has hundreds digit
- t tells the last two digits
- tens1 tells the tens digit
- tens2 tells the ones digit
- So for an input number like 987, the 4 values would be h=9, t=87, tens1=8 and tens2=7
- Now, construct the words version of number by simply concatenating below:
- INDEX(upto19, h)
- ” hundred “
- if tens1<2 then INDEX(upto19, t)
- else INDEX(tys, tens1-1)&”-“&INDEX(upto19, tens2)
The actual formula needs a few more if conditions to stop the flow when you hit a round number (like 500 should five hundred with no other words after).
The process for numbers up to 999,999:
We just need to follow the same idea as above, but twice. Once for thousands and once for balance.
Known limitations of this formula:
- This formula works up to numbers 999,999 only. You can scale it up to work with numbers up to a billion easily, but the formula gets longer.
- It ignores any portion after decimal point. So 1003.20 becomes one thousand three.
- It doesn’t show “zero” for 0 input value. The output would be blank instead.
Excel Number to Words Formula - Video Explanation
Just in case your head hurts after all that explanation above, watch this video to understand how the formula works (plus a quick demo of LET function).
Excel Number to Words - VBA User Defined Formula
If you are not able to use the formula version then consider using below VBA UDF to convert number to words. This works up to a billion.
Public Function number2words(thisNum As Double) As String
Dim bn As Integer, mn As Integer, th As Integer, h As Integer, retval As String
On Error GoTo msg
bn = Int(thisNum / 1000000000)
mn = Int(thisNum / 1000000) Mod 1000
th = Int(thisNum / 1000) Mod 1000
h = thisNum Mod 1000
If bn >= 1 Then
retval = num2words999(bn) & " billion "
End If
If mn >= 1 Then
retval = retval & num2words999(mn) & " million "
End If
If th >= 1 Then
retval = retval & num2words999(th) & " thousand "
End If
retval = retval & num2words999(h)
number2words = retval
Exit Function
msg:
number2words = "error"
End Function
Private Function num2words999(thisNum As Integer) As String
'convert any number up to 999 to words
Dim tys As Variant, upto19 As Variant
tys = Array("twenty", "thirty", "fourty", "fifty", "sixty", "seventy", "eighty", "ninety")
upto19 = Array("one", "two", "three", "four", "five", "six", "seven", "eight",
"nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen",
"sixteen", "seventeen", "eighteen", "nineteen")
Dim h As Integer, t As Integer, tens1 As Integer, tens2 As Integer, retval As String
h = Int(thisNum / 100) Mod 10
t = thisNum Mod 100
tens1 = Int(t / 10)
tens2 = t Mod 10
If h >= 1 Then
retval = upto19(h - 1) & " hundred "
End If
If t >= 1 Then
If tens1 < 2 Then
retval = retval & upto19(t - 1)
Else
retval = retval & tys(tens1 - 2)
If tens2 >= 1 Then
retval = retval & "-" & upto19(tens2 - 1)
End If
End If
End If
num2words999 = retval
End Function
How to use the Number2Words UDF?
You do not need prior VBA knowledge to use this function. It works like any other Excel function once you install it.
To install Number2Words function:
- You must have Personal Macros enabled. If not, click here to read on how to do that.
- Go to your Personal Macros workbook. Add a module or open an existing module.
- Paste the above code there.
How to use the Number2Words function:
To use this function on value in A1, simply write =Number2Words(A1)
Known limitations:
- This function ignores any portion after decimal point
- It doesn’t say “Zero” if input is 0 or blank. It would simply return blank.
- The results are not capitalized. Use Excel functions like PROPER() to do that.
- This function works up to 2 billion.
- If you email the result workbook to a colleague or client, then they cannot refresh the formula (unless they too have installed Number2Words UDF)
FREE VBA Crash Course
If you are curious about VBA, click here for my free 5 part crash course.
Download File - Number to Words Formula & UDF
Formula & UDF to get Words from Number
Click here to download the sample file for this page. You can see number to words formula in column C. Column E has VBA version output. Play with the formulas or examine the code to learn more.
Comments or suggestions please....
I hope you are finding the number to words formulas useful. Let me know if you are facing any issues or have suggestions for changing the outputs.
21 Responses to “Distinct count in Excel pivot tables”
The distinct count option works well but I have found that if I have a date field and want to group by year, month, etc. that option seems to be disabled. I need to do both, distinct count and group by year/month.
Example data; sales orders with item quantities with dates.
Challenge; sum the item quantities, count the distinct orders and group by month. How do I do this?
Perhaps that's not possible due to the grouping?
@Al... When you use data model based pivots, you cannot group values manually anymore. Why not use Excel 2016's default date grouping option? In this case we have just a few dates, so Excel is not grouping them, but if you have an year's worth of data, when you make the pivot with date in the row label area, Excel automatically groups them. If you have fewer dates or want to use your own grouping, just create a table with all dates, add columns with month, week, year etc. Then connect this table (these types of tables are usually called as calendar tables) to your data on date field as a relationship. Now you can create reports by month, quarter etc easily.
Is this the only way to do it in 2013? I find it rather cumbersome to have to create another data table listing dates with the another column for MONTH() and YEAR() to be able to summarise data for senior level...
I know people find adding calendar tables cumbersome, but it is a best practice and let's you add more layers of analysis quite easily. For example, adding analysis by weekday vs. weekend or by financial quarter or YTD calculations (you would need either Power Pivot DAX or some very carefully setup pivot table value field settings)
I had absolutely no idea this was possible. Very useful, nice work!
Doesn't work for 2010 version though (or at least not my works version)
Hi ,
The post has the following in it :
These instructions work only in Excel 2016, Office 365 and Excel 2013.
when i have 2 different Pivot tables, one without the enabled “Add this data to data model” option, and the other one with it enabled.. is there anyway i can link slicers between them?
if the answer is NO,, what to do ?
Quick note, the “Add this data to data model” option is not available for the Mac version.
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?
Is there a way to still add a calculated field when using distinct count?
I found I can't change the date source after tick the " add this data to the data model", can you help to adv how to change the date source in such case?
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance.
Powerful Introduction to Power Query
A big Thank you. It worked.
Hi, have survey data that I need to analyze but the challenge is that my key fields are showing horizontally. I tried to transpose the fields using Power Query, but unfortunately the new fields are returning same values on a pivot table despite using distinct values
How I can a do a pivot table with discount conts in some columns and then generate shor report filter pages. pls it drives crazy
Hi. Why grand total pivot of distinct count is 13? shouldn't it be 67?
Great Answer! Saved me lots of time!
Thank you!!!
Worked awesome! Thanks!!
Hi Chandoo,
I am using pivot tables for distinct count and now I need to update them with new set of data. But when I update the source data, all the columns and formatting of Pivot table disappears and I need to build it from Scratch.
Is there a possibility that I can update the source data with new rows added and also retain my pivot tables?