How to Convert a Numeric Value into English Words in Excel

In this article, we will learn how to convert a numeric value into English words in excel in 2 suitable ways. In our daily life, we often need to convert a numeric value into English words. We can use it frequently in shops, educational institutes, and industries. We can easily convert a numeric value into English words in Microsoft Excel. Are you having trouble converting a numeric value into English words?


Download Practice Workbook

You can download the Excel workbook from here.


2 Suitable Ways to Convert a Numeric Value into English Words in Excel

We can convert a numeric value into English words in 2 ways in Microsoft Excel. The first method is by using VBA Code and the second method is by using Excel formula. Both methods have their own pros and cons. Suppose, we have a dataset like an image below and we want to convert these numeric values into English words. For better understanding, we took integers and numbers with decimals both. Now we will see how to convert these numeric values into English words in Excel.

2 Suitable Ways to Convert a Numeric Value into English Words in Excel


1. Use VBA Code to Convert a Numeric Value into English Words in Excel

Using VBA Code to convert a numeric value into English words is the most common approach in Excel. Actually, Excel needs a function named SpellNumber to convert a numeric value into English words. But this function is not an in-built function in Excel. First, we have to create a user-defined function named SpellNumber using VBA Code. Then we will be able to use the SpellNumber function to convert a numeric value into English words. In order to do so, follow the steps below.

Steps:

  • Firstly, open a blank workbook in Microsoft Excel.
  • Then, press Alt + F11 to open the Microsoft Visual Basic editor.
  • Furthermore, click on the Insert tab and click Module as shown below.

Use VBA Code to Convert a Numeric Value into English Words in Excel

  • After that, copy and paste the following VBA code into your module as shown below.
Option Explicit
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

Use VBA Code to Convert a Numeric Value into English Words in Excel

  • Furthermore, press Ctrl + S to save the VBA Code in your workbook.
  • Hence, it will open the Save As window like the image below.
  • Next, type the file name as you want and click on the drop-down of the Save as type as shown below.

Use VBA Code to Convert a Numeric Value into English Words in Excel

  • Afterward, select the Excel Macro-Enabled Workbook (*.xlsm) from the drop-down menu as your workbook contains VBA Code.

Use VBA Code to Convert a Numeric Value into English Words in Excel

  • Furthermore, click on the Save option.

  • As a result, your VBA code will be saved in your workbook and a user-defined function SpellNumber will be added.
  • Moreover, go to your workbook.
  • Then, select the C5 cell and type the following formula, and press Enter:
=SpellNumber(B5)
  • Hence, your numeric value will be converted into English words as shown below.

  • Subsequently, to do the same for all the numbers, select the C5 cell and drag the Fill Handle to the entire column like the image below.
  • Finally, all the numbers will be converted into English words like the below one.


Disadvantages of Using VBA Code Method

  • To begin with, you have to know VBA to understand the coding and modify it according to your requirements.
  • Also, you have to paste the code to each workbook in which you want to convert a numeric value into English words.
  • The main drawback of this method is when you share the file with anyone else or open the file on another device, it will show a Microsoft Excel Security Notice like the image below. You have to click the Enable Macros option every time you see the notice to overcome this problem.

Disadvantages of Using VBA Code Method

Read More: Excel VBA to Convert Number to Text (4 Examples)


2. Convert a Numeric Value into English Words in Excel Using formula

We can also use a formula to convert a numeric value into English words in Excel. Persons who are not familiar with VBA codes can apply this method to convert a numeric value into English words in Excel. In order to do so, follow the steps below.

Steps:

  • First of all, select the C5 cell and type the following formula:

=CHOOSE(LEFT(TEXT(B5,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(B5,"000000000.00"))=0,,IF(AND(--MID(TEXT(B5,"000000000.00"),2,1)=0,--MID(TEXT(B5,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(B5,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B5,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B5,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B8,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&IF((--LEFT(TEXT(B5,"000000000.00"))+MID(TEXT(B5,"000000000.00"),2,1)+MID(TEXT(B5,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B5,"000000000.00"),4,1)+MID(TEXT(B5,"000000000.00"),5,1)+MID(TEXT(B5,"000000000.00"),6,1)+MID(TEXT(B5,"000000000.00"),7,1))=0,(--MID(TEXT(B5,"000000000.00"),8,1)+RIGHT(TEXT(B5,"000000000.00")))>0)," Million and "," Million "))
&CHOOSE(MID(TEXT(B5,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(B5,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(B5,"000000000.00"),5,1)=0,--MID(TEXT(B5,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))
&CHOOSE(MID(TEXT(B5,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(B5,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B5,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B5,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(B5,"000000000.00"),4,1)+MID(TEXT(B5,"000000000.00"),5,1)+MID(TEXT(B5,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B5,"000000000.00"),7,1)+MID(TEXT(B5,"000000000.00"),8,1)+MID(TEXT(B5,"000000000.00"),9,1))=0,--MID(TEXT(B5,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))
&CHOOSE(MID(TEXT(B5,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(B5,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B5,"000000000.00"),8,1)=0,--MID(TEXT(B5,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&
CHOOSE(MID(TEXT(B5,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B5,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B5,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B5,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

&" & "&RIGHT(TEXT(B5,"000000000.00"),2)&"/100"

  • In addition, press Enter.
  • As a result, your numeric value will be converted into English words like the image below.

Convert a Numeric Value into English Words in Excel Using formula

  • After that, in order to do the same for all the numbers, select the C5 cell and drag the Fill Handle to the entire column as shown below.
  • Finally, all the numbers will be converted into English words like the image below.

🔎 How Does the Formula Work?
The formula has four key functions:

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

CHOOSE(LEFT(TEXT(B5),”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B5),”000000000.00″))=0,,IF(AND(–MID(TEXT(B5),”000000000.00″),2,1)=0,–MID(TEXT(B5),”000000000.00″),3,1)=0),” Hundred”,” Hundred and “)):
When this part of the formula works, we will find out the remainder of the formula as the operation will repeat.
The LEFT Function
The LEFT function’s purpose is to choose a specific number of characters from the text’s left side. The structure of the LEFT function is as follows.
LEFT(text, [num_chars]):

Text parameter refers to the cell that contains the input, and [num chars] defines how many characters should be extracted. Since the [num chars] parameter is optional, leaving it off will result in a default drawing of just one character.
The MID Function
The objective of the MID function is to draw out a specific number of characters from a specific character position of the text from the left side. The MID function has the following structure.
MID(text, start_num, num_chars):
Text parameter mentions the cell carrying the input; the parameter start_num specifies the position to begin text drawing out by counting from the left side, while num_chars specifies the number of characters to draw out.
The TEXT Function

The TEXT function’s prime objective is to mention a cell’s details with specific formatting. The structure of the TEXT function is as follows.

TEXT(value, format_text):
Common cell formatting controls are typically used to apply formatting to a cell. The format is followed exactly by the TEXT. In this manner, the formatting of the information can be altered dynamically depending on your needs and the circumstances. In our case, the following function:
TEXT(B5,”000000000.00”):
It adds leading zeroes to the inserted number to pad it. By counting the leading zeroes, the algorithm will determine if the value is in the hundreds, thousands, or millions. For instance, the TEXT method will interpret a user-inputted number like 589768.67 as 000589768.67. With three leading zeroes, it denotes a number in the thousands.
The CHOOSE Function
The CHOOSE function streamlines numerously nested IF functions that examine the same set of data. When using indexes, the CHOOSE function is extremely useful. The CHOOSE function’s goal is to choose a value from a built-in list of values based on a supplied integer.
CHOOSE(index_num, value1, [value2], …):

What would the result of the CHOOSE function be if we were to remove the first (left-most) number from the inserted number and pass it to it?

CHOOSE(LEFT(B5), “One”, “Two”, “Three”, “Four”, “Five”, “Six”, “Seven”, “Eight”, “Nine”):
The drawn-out number’s word representation will then be seen. The numbers “3” and “7” would be shown as “Three” and “Seven,” respectively.
We are combining all the functions into one formula in this formula, and each function will play a specific part in carrying out the mission.
CHOOSE(LEFT(TEXT(B5,”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B5,”000000000.00″))=0,,IF(AND(–MID(TEXT(B5,”000000000.00″),2,1)=0,–MID(TEXT(B5,”000000000.00″),3,1)=0),” Hundred”,” Hundred and “)):
To start, we’ll format the integer as “000000000.00” using the TEXT function.
TEXT(B5,”000000000.00″):
The character is then taken out of the number on the left.
LEFT(TEXT(B5,”000000000.00″)):
We can use it to determine whether the returned number is zero or another value. This will indicate whether or not the number falls inside the “millions” range. The drawn-out value will then be indicated in a manner that we will select.
CHOOSE(LEFT(TEXT(B5,”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”):
Next, we check whether the value is zero or not.
CHOOSE(LEFT(TEXT(B5,”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B5,”000000000.00″))=0,,:

If the value is zero, this means that it is not in the millions, and nothing will be displaye. In the event that the next two numbers are zero, we shall put “Hundred” in the display. CHOOSE(LEFT(TEXT(B5,”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B5,”000000000.00″))=0,,IF(AND(–MID(TEXT(B5,”000000000.00″),2,1)=0,–MID(TEXT(B5,”000000000.00″),3,1)=0),” Hundred”,” Hundred and “)):
We will get “Hundred and” in the display if the next two digits are not zero.

Read More: How to Convert Peso Number to Words in Excel (With Easy Steps)


Things to Remember

  • If you are someone who has a basic knowledge of VBA, then the VBA Code method will be a good option for you to convert a numeric value into English words in Excel.
  • We can use the VBA method to convert both integers and numeric values with multiple decimals.
  • One can use the Excel formula method to convert integers and numeric values up to two decimals If you take numerical values with more than two decimals, it will round the value up to two decimals and convert it into English words.
  • Also, you can modify the VBA code and the Excel formula as per your dataset.

Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to apply center horizontal alignment in Excel. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. So, don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo