Convert Number to Words in Excel
In this part of our tutorial blog, you will learn and understand about utilizing excel formula to convert number to words. Convert number to words in excel is not a built-in feature in the software. Because of that, what will be given here is the VBA code to create a customized formula so the automation process to convert number to words in excel can exist. After putting the VBA code in your spreadsheet, you can then use the formula from the code just like any other formula in the software.
Why We Need to Learn About Excel Convert Number to Words?
When working on data in the form of digits, especially related to money, often we need to make the word form of the digit. For one or two static digit data in a simple form, it will probably be not too much of a problem to do it manually. But when you face dynamic digits of money that keep changing, then it will be much more effective and efficient if there is a tool that can help to make the process automatic.
If we need to do that kind of digit processing in this spreadsheet software, there is no special formula to help the conversion process from a digit to words. But what can help us with this matter is we can create our own formula in excel according to our needs. Therefore, if we often need to convert number to words in excel, then the formulation of an excel formula for that matter will become important for us to do and understand. Thus, Compute Expert provides the VBA code in this tutorial for customized excel convert number to words facilitation which can be taken for you to be used in your spreadsheet.
What is Convert Number to Words in Excel?
Convert number to words in excel is a customized formula from VBA code which can be used to convert figures to words in excel, usually needed if we process digits related to money. After inputting the VBA code given below in your spreadsheet, the formula to convert number to words in excel can be used just like any regular formula with the input of the formula is the digit to be processed.
Briefly, the input of this customized formula is explained as follows:
=NUMBERTOTEXT(value)
Keterangan:
value = the number that you want to convert to words
How to Create and Use Formula for Number to Words in Excel?
Next part will explain about how to formulate the formula for the amount in words in excel facilitation in your VBA editor and how to use it
Check and Set Macro Security Setting
To be able to use a customized formula from a VBA code, you need to permit macro to be able to function in your excel. Therefore, we need to make sure about this permission setting first before you can input the code to create a formula to convert number to words in excel-
Click File from menu tab
-
Click Options
-
Click Trust Center
-
Click Trust Center Settings...
-
Click Macro Settings
-
Click Enable All Macros choice
-
Click OK and OK
-
Click File then Save As
-
On the Save as Type: dropdown, choose Excel Macro-Enabled Workbook
-
Name your file in text input column File Name:
-
Click Save
Create the Formula to Convert Number to Words in Excel
After setting the macro permission, it is time to create excel convert number to words formula in your file by using VBA editor- Press Alt and F11 at the same time on your keyboard
-
Right click on “VBA Project…”
-
Highlight Insert with your pointer and click Module
-
Double Click on the Module menu which comes up and input this code in the right side part of the VBA editor screen
Function NUMBERTOTEXT(ByVal n As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion
If (n = 0@) Then NUMBERTOTEXT = "zero": Exit Function
Dim Buf As String: If (n < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(n - Fix(n))
If (n < 0@ Or Frac <> 0@) Then n = Abs(Fix(n))
Dim AtLeastOne As Integer: AtLeastOne = n >= 1
If (n >= Trillion) Then
Buf = Buf & EnglishDigitGroup(Int(n / Trillion)) & " trillion"
n = n - Int(n / Trillion) * Trillion
If (n >= 1@) Then Buf = Buf & " "
End If
If (n >= Billion) Then
Buf = Buf & EnglishDigitGroup(Int(n / Billion)) & " billion"
n = n - Int(n / Billion) * Billion
If (n >= 1@) Then Buf = Buf & " "
End If
If (n >= Million) Then
Buf = Buf & EnglishDigitGroup(n \ Million) & " million"
n = n Mod Million
If (n >= 1@) Then Buf = Buf & " "
End If
If (n >= Thousand) Then
Buf = Buf & EnglishDigitGroup(n \ Thousand) & " thousand"
n = n Mod Thousand
If (n >= 1@) Then Buf = Buf & " "
End If
If (n >= 1@) Then
Buf = Buf & EnglishDigitGroup(n)
End If
If (Frac = 0@) Then
Buf = Buf
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & " "
Buf = Buf & Format$(Frac * 100@, "00") & "/100"
Else
If AtLeastOne Then Buf = Buf & " "
Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
End If
NUMBERTOTEXT = Buf
End Function
Private Function EnglishDigitGroup(ByVal n As Integer) As String
Const Hundred = "hundred"
Const One = "one "
Const Two = "two "
Const Three = "three "
Const Four = "four "
Const Five = "five "
Const Six = "six "
Const Seven = "seven "
Const Eight = "eight "
Const Nine = "nine "
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False
Select Case (n \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select
If (Flag <> False) Then n = n Mod 100
If (n > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If
Select Case (n \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select
If (Flag <> False) Then n = n Mod 10
If (n > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If
Select Case (n)
Case 0:
Case 1: Buf = Buf & "one"
Case 2: Buf = Buf & "two"
Case 3: Buf = Buf & "three"
Case 4: Buf = Buf & "four"
Case 5: Buf = Buf & "five"
Case 6: Buf = Buf & "six"
Case 7: Buf = Buf & "seven"
Case 8: Buf = Buf & "eight"
Case 9: Buf = Buf & "nine"
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"
Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select
EnglishDigitGroup = Buf
End Function
-
Click on the X mark in the red box on the most top right to close the VBA editor. The formula to convert number to words in excel is now ready to use!
Use Excel Convert Number to Words Formula
After inputting the code to create the formula to convert number to words in excel, it is time to use it-
Type equal sign ( = ) in the cell where you want to put the result of the formula to convert number to words in excel
-
Type NUMBERTOTEXT (can be with large and small letters) and open bracket sign after =
-
Type digit / cell coordinate where the digit which you want to process is after open bracket sign
-
Type close bracket sign
- Press Enter
-
The process is done!
Exercise
After you have learned how to convert number to words in excel, you can practice your understanding about it through this exercise!
Download the file from the following link and do as instructed. Download the answer if you have done the exercise and sure about the results!
Link to download the exercise file:
Download here
Instruction:
Use excel convert number to words function to read the digits in first, second, and third columns!
Link to download answer:
Download here
Additional Notes
- This formula to convert number to words in excel cannot be used to read decimal digits
- This formula to convert number to words in excel is not automatically there every time you open this spreadsheet software after the first time you input VBA code to create it. If you want to use it in other files, you need to input it again by redoing the steps in this tutorial