Excel: How To Convert Number into Words | Acme Collins School

Excel: How To Convert Number into Words

Do you know how to convert number into words in excel?

If you’re unaware, you’ve come to the right place. Here, we will learn how to convert numbers into words in Microsoft Excel.

Before we begin, I ask simply, “Have you ever used Microsoft Excel?” If you have, then you should be familiar with the functions and formulas used in Excel.

And if you don’t know that’s a difference case. and you need to learn it from the basic and then you will be able to understand the uses and functionalities of Microsoft Excel and the formulae it uses. So, if you want to learn Microsoft Excel from the very basics you can enroll in Advanced Excel Course. Where you will learn the basics and the advanced uses of Microsoft Excel.

Now, Let’s see further steps that we can use to convert numbers into words. as we have understood that excel does not have this built-in function. So, we need to use excel VBA code editor? Now, you must be thinking that “What is VBA?” So let’s understand it

What is VBA?

I hope you have understood the VBA. Now let’s continue to our topic.

So, for converting numbers into words you need to write some code into VBA. and after that you will be able to use formula [=NumberToWords(A2)]. Let’s see a step-by-step guide how to write this code into VBA.

Step-by-Step Guide to Create a Custom VBA Function

  1. Open Excel and press Alt + F11 to open the VBA editor.
  2. Insert a new module by clicking on Insert > Module.
  3. Copy and paste the following VBA code into the module:
Function NumberToWords(ByVal MyNumber)
Dim Units As String
Dim Teens As String
Dim Tens As String
Dim Hundreds As String
Dim Temp As String
Dim DecimalPlace As Integer
Dim Count As Integer
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' Convert MyNumber to string
MyNumber = Trim(CStr(MyNumber))
DecimalPlace = InStr(MyNumber, ".")

' Convert decimal part
If DecimalPlace > 0 Then
Temp = " point"
For Count = DecimalPlace + 1 To Len(MyNumber)
Temp = Temp & " " & Mid(MyNumber, Count, 1)
Next Count
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Units = Temp & Place(Count) & Units
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

NumberToWords = Application.Trim(Units) & Temp
End Function

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

' Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

' Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Function GetTens(TensText)
Dim Result As String
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1))
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
  1. Save the module and close the VBA editor.

How to Use the Custom Function

  1. Return to Excel and select the cell where you want to convert a number to words.
  2. Enter the custom function. For example, if you want to convert the number in cell A1, enter the following formula in the selected cell:
=NumberToWords(A1)
  1. Press Enter. The number in cell A1 will be converted to words and displayed in the cell with the formula.

Example

  • If A1 contains 123, the formula =NumberToWords(A1) will display “One Hundred Twenty Three”.

With this custom VBA function, you can easily convert numbers to words in Excel, enhancing your data presentation and reporting.

Bonus Tips

While performing this action, you won’t be able to save your custom function in Excel. To save the custom function, you must save your file as a macro-enabled document. Refer to the screenshot below for guidance.

Happy Learning ☺️

Scroll to Top