How to Convert Number to Words in Excel Without VBA

We often need to convert a number to words in Excel for various purposes. We can use it frequently in shops, educational institutes, banks, etc. A popular way to do this is using Excel. But there are two ways to convert from numbers to words. One is using the VBA code and the other is without VBA. In this article, we will learn how to convert numbers to words in excel without VBA. We will also show how to do it with VBA as well for better understanding.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Convert Number to Words in Excel Without VBA

We will use a formula to convert number to words in excel without VBA. Since Excel may contain very big numbers, the formula needs to be built in such a way that it can convert any number to words. So our first step should be building the formula. And the next step is to implement it in Excel to convert number to words without VBA.

Here we can consider the following Dataset. Also, we here have some numbers that we want to convert to words.


Step 1: Building a Formula to Convert from Number to Words

Here we will be using a combined formula that can cover bigger numbers as well. So the formula is huge but it does give us the expected output.

The formula is:

=IF(OR(LEN(FLOOR(B5,1))=13,FLOOR(B5,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(B5>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),IF(VALUE(MID(TEXT(INT(B5),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(B5),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(B5,1)>1," "," "))&IF(ISERROR(FIND(".",B5,1)),""," point "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&" ","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"One","One One","One Two","One Three","One Four","One Five","One Six","One Seven","One Eight","One Nine")&" ","Two","Three","Four","Five","Six","Seven","Eight","Nine"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,""," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine")&" ",IF(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)="01","one ",IF(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE('Number to Word Without VBA'!B5,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&" ","")))))))

Read More: How to Convert Number to Text in Excel (4 Ways)


Step 2: Applying the Formula in Excel

To apply this formula in excel and convert numbers to words in Excel, we will follow the steps below:

  • At first, we need to select the cell where we want the converted word of a number. In our case it’s C5.
  • In addition, copy the entire formula and paste it into the C5 cell.

Step-by-Step Procedures to Convert Number to Words in Excel Without VBA

  • As a result, pressing Enter will give us the converted word output of the adjacent B5 cell’s numeric value.

Step-by-Step Procedures to Convert Number to Words in Excel Without VBA

  • Further, select cell C5, then drag the Fill Handle to the bottom where our members end. In our case, we need to pull it down to C13.

  • At last, we will have the result as follows.

Step-by-Step Procedures to Convert Number to Words in Excel Without VBA

🔎 How Does the Formula Work?

The formula has four key functions:

  • LEFT Function
  • TEXT Function
  • MID Function
  • CHOOSE Function

The Left Function

The goal of the LEFT function is to select a predetermined number of characters from the left side of the text. The LEFT function is organized as follows.

LEFT(text, [num_chars]):

The text parameter is the cell that has the input, and [num_chars] is the number of characters to pull out. Since the [num chars] parameter is optional, if you don’t use it, the program will draw just one character by default.

The Text Function

The main purpose of the TEXT function is to put information about a cell in a certain way. Here’s how the TEXT function is put together.

TEXT(value, format_text):

Most of the time, you use common cell formatting controls to change how a cell looks. The TEXT sticks to the format of the letter. In this way, the information can be formatted in a way that changes based on your needs and the situation.

The MID Function

The goal of the MID function is to pull a certain number of characters from a certain character position on the left side of the text. This is how the MID function is put together.

MID(text, start_num, num_chars):

The input cell is named Text. The start_num argument informs the software where to start drawing text. The num_chars specify how many characters to draw.

The CHOOSE Function

The CHOOSE function makes it easier to check the same set of data with many nested IF functions. This function is very useful when working with indexes. The goal of the CHOOSE function is to pick a value from a list of built-in values based on an integer that you give it.  So, this is how the CHOOSE function is put together.

CHOOSE(index_num, value1, [value2], …):

Read More: How to Convert a Numeric Value into English Words in Excel


Convert Number to Words in Excel with VBA

We can convert numbers to words using the VBA code in Excel as well. To do so, we need to follow these steps.

Steps:

  • Firstly, go to the Developer tab in the Ribbon tab and click on Visual Basic. A new window will appear.

Convert Number to Words in Excel With VBA

  • Secondly, in the window click on Insert and then click on Module.

Convert Number to Words in Excel With VBA

  • Thirdly, copy the following code and paste it into the window:
Function SpellNumber(ByVal MyNumber)
Dim uStr As String
Dim uFNum As Integer
Dim uStrPoint
Dim uStrNumber
Dim uPoint As String
Dim uNumber As String
Dim uP() As Variant
Dim uDP
Dim uCnt As Integer
Dim uResult, uT As String
Dim uLen As Integer
On Error Resume Next
uP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
uNumber = Trim(Str(MyNumber))
uDP = InStr(uNumber, ".")
uPoint = ""
uStrNumber = ""
If uDP > 0 Then
uPoint = " point "
uStr = Mid(uNumber, uDP + 1)
uStrPoint = Left(uStr, Len(uNumber) - uDP)
For uFNum = 1 To Len(uStrPoint)
uStr = Mid(uStrPoint, uFNum, 1)
uPoint = uPoint & Digits(uStr) & " "
Next uFNum
uNumber = Trim(Left(uNumber, uDP - 1))
End If
uCnt = 0
uResult = ""
uT = ""
uLen = 0
uLen = Int(Len(Str(uNumber)) / 3)
If (Len(Str(uNumber)) Mod 3) = 0 Then uLen = uLen - 1
Do While uNumber <> ""
If uLen = uCnt Then
uT = HundredsDigits(Right(uNumber, 3), False)
Else
If uCnt = 0 Then
uT = HundredsDigits(Right(uNumber, 3), True)
Else
uT = HundredsDigits(Right(uNumber, 3), False)
End If
End If
If uT <> "" Then
uResult = uT & uP(uCnt) & uResult
End If
If Len(uNumber) > 3 Then
uNumber = Left(uNumber, Len(uNumber) - 3)
Else
uNumber = ""
End If
uCnt = uCnt + 1
Loop
uResult = uResult & uPoint
SpellNumber = uResult
End Function
Function HundredsDigits(uHDgt, uB As Boolean)
Dim uRStr As String
Dim uStrNum As String
Dim uStr As String
Dim uI As Integer
Dim uBB As Boolean
uStrNum = uHDgt
uRStr = ""
On Error Resume Next
uBB = True
If Val(uStrNum) = 0 Then Exit Function
uStrNum = Right("000" & uStrNum, 3)
uStr = Mid(uStrNum, 1, 1)
If uStr <> "0" Then
uRStr = Digits(Mid(uStrNum, 1, 1)) & "Hundred "
Else
If uB Then
uRStr = "and "
uBB = False
Else
uRStr = " "
uBB = False
End If
End If
If Mid(uStrNum, 2, 2) <> "00" Then
uRStr = uRStr & TenDigits(Mid(uStrNum, 2, 2), uBB)
End If
HundredsDigits = uRStr
End Function
Function TenDigits(uTDgt, uB As Boolean)
Dim uStr As String
Dim uI As Integer
Dim uArr_1() As Variant
Dim uArr_2() As Variant
Dim uT As Boolean
uArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
uArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
uStr = ""
uT = True
On Error Resume Next
If Val(Left(uTDgt, 1)) = 1 Then
uI = Val(Right(uTDgt, 1))
If uB Then uStr = "and "
uStr = uStr & uArr_1(uI)
Else
uI = Val(Left(uTDgt, 1))
If Val(Left(uTDgt, 1)) > 1 Then
If uB Then uStr = "and "
uStr = uStr & uArr_2(Val(Left(uTDgt, 1)))
uT = False
End If
If uStr = "" Then
If uB Then
uStr = "and "
End If
End If
If Right(uTDgt, 1) <> "0" Then
uStr = uStr & Digits(Right(uTDgt, 1))
End If
End If
TenDigits = uStr
End Function
Function Digits(uDgt)
Dim uStr As String
Dim uArr_1() As Variant
uArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
uStr = ""
On Error Resume Next
uStr = uArr_1(Val(uDgt))
Digits = uStr
End Function
  • After that, save the file as a macro-enabled Excel file with xlsm extension.
  • Then select the C5 And in the cell and write ‘=SpellNumber(B5)’. Since our numeric data is in B5, here we wrote B5.

Convert Number to Words in Excel With VBA

  • Furthermore, by pressing Enter, We will get the word converted version of the number.

  • Finally, drag the Fill Handle from C5 to C13 as before. We will have our results.

By pressing Enter, We will get the word converted version of the number.

Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)


Things to Remember

  • While practicing, we need to change the cell value of the formula. We need to replace each cell value with the cell you want.
  • Moreover, the formula can show output only up to 2 decimal places.
  • For higher decimal places, we need to go with the VBA method.

Conclusion

Number to words in Excel is a useful topic in terms of education and banking. Hope this article will help you with this purpose. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel related problems, you can visit our website Exceldemy for all types of excel related problem solutions.


Related Articles

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo