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.

Advanced Excel
Master advanced Excel skills: functions, data analysis, automation, charts, and more. Unlock Excel’s full potential. Enroll now and excel in your work!
Buy Now

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?

VBA (Visual Basic for Applications) code editor is an integrated development environment (IDE) within Microsoft Excel that allows you to write, edit, and manage VBA code. VBA is a programming language used to automate tasks and create custom functions and macros in Excel and other Microsoft Office applications.

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