Converting Numbers to Words in Excel with VBA: A Step-by-Step Guide

If you’ve ever wanted to convert numbers into words in Excel, VBA (Visual Basic for Applications) can make it happen. Whether it’s for financial reports, invoices, or educational purposes, you can use VBA to spell out numbers in words. Let’s dive into four easy-to-use VBA scripts that handle different scenarios. I’ll also explain how to implement and use each one with examples. Let’s go!

Step-by-Step Guide to Create a VBA Function for Number to Words Conversion

Open the VBA Editor:
- Press “Alt + F11” (windows) “Fn + Option + F11” (MAC) to open the VBA editor.
Insert a New Module:
- In the VBA editor, go to Insert > Module to create a new module.
Copy and Paste the VBA Code:

Save and Close the VBA Editor:
- Save your work and close the VBA editor.

Click on the VBA script you like to use and give it a try.

VBA to convert Whole Numbers to words

This script converts whole numbers into words. For example, 1234 becomes One Thousand Two Hundred Thirty-Four.

VBA Code

Function NumberToWords(ByVal MyNumber)
    Dim TempStr 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 and trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find position of decimal place (ignore decimals)
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

    Count = 1
    Do While MyNumber <> ""
        ' Convert last 3 digits of MyNumber to text.
        If Val(Right(MyNumber, 3)) > 0 Then
            TempStr = ConvertHundreds(Right(MyNumber, 3)) & Place(Count) & IIf(Count > 1, ", ", " ") & TempStr
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    ' Remove any leading or trailing spaces
    NumberToWords = Application.Trim(TempStr)
End Function

Function ConvertHundreds(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 & ConvertTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If

    ConvertHundreds = Result
End Function

Function ConvertTens(ByVal MyTens)
    Dim Result As String
    If Val(Left(MyTens, 1)) = 1 Then
        Select Case Val(MyTens)
            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(MyTens, 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(MyTens, 1))
    End If
    ConvertTens = Result
End Function

Function GetDigit(ByVal MyDigit)
    Select Case Val(MyDigit)
        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

Example:

Enter a number in a cell (e.g., 1234 in cell A1).
In another cell, type the formula:=NumberToWords(A1)
Press Enter, and Excel will display One Thousand Two Hundred Thirty-Four.

VBA to convert Decimal Numbers to words

This script handles numbers with decimals, spelling the integer and decimal parts separately. For example, 1234.56 becomes One Thousand Two Hundred Thirty-Four point Five Six.

VBA Code

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Teens As String
    Dim Tens As String
    Dim Thousands As String
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer

    ' Convert MyNumber to string and trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find Position of decimal place (0 if none).
    DecimalPlace = InStr(MyNumber, ".")

    ' If there's a decimal, process the integer and fractional parts separately
    If DecimalPlace > 0 Then
        ' Process integer part
        TempStr = ConvertIntegerPart(Left(MyNumber, DecimalPlace - 1))
        ' Process fractional part
        TempStr = TempStr & " point " & ConvertDecimalPart(Mid(MyNumber, DecimalPlace + 1))
    Else
        ' No decimal, just process the integer part
        TempStr = ConvertIntegerPart(MyNumber)
    End If

    ' Remove any leading or trailing spaces
    NumberToWords = Application.Trim(TempStr)
End Function

Function ConvertIntegerPart(ByVal MyNumber)
    Dim Place(9) As String
    Dim TempStr As String
    Dim Count As Integer

    Place(2) = " Thousand"
    Place(3) = " Million"
    Place(4) = " Billion"
    Place(5) = " Trillion"

    Count = 1
    Do While MyNumber <> ""
        ' Convert last 3 digits of MyNumber to text.
        If Val(Right(MyNumber, 3)) > 0 Then
            TempStr = ConvertHundreds(Right(MyNumber, 3)) & Place(Count) & IIf(Count > 1, ", ", " ") & TempStr
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    ' Trim final result
    ConvertIntegerPart = Application.Trim(TempStr)
End Function

Function ConvertDecimalPart(ByVal MyDecimal)
    Dim i As Integer
    Dim Result As String
    For i = 1 To Len(MyDecimal)
        Result = Result & GetDigit(Mid(MyDecimal, i, 1)) & " "
    Next i
    ConvertDecimalPart = Application.Trim(Result)
End Function

Function ConvertHundreds(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 & ConvertTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If

    ConvertHundreds = Result
End Function

Function ConvertTens(ByVal MyTens)
    Dim Result As String
    If Val(Left(MyTens, 1)) = 1 Then
        Select Case Val(MyTens)
            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(MyTens, 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(MyTens, 1))
    End If
    ConvertTens = Result
End Function

Function GetDigit(ByVal MyDigit)
    Select Case Val(MyDigit)
        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 0: GetDigit = "Zero"
        Case Else: GetDigit = ""
    End Select
End Function

Example:

Enter a number in a cell (e.g., Input: 1234.56 in A1).
In another cell, type the formula:=NumberToWords(A1)
Output: One Thousand Two Hundred Thirty-Four point Five Six

VBA to convert Whole Numbers
(With Currency Format) to words

This script spells out whole numbers with a currency name. For example, 1234 becomes One Thousand Two Hundred Thirty-Four Dollars.

VBA Code

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Teens As String
    Dim Tens As String
    Dim Thousands As String
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim CurrencyName As String
    Dim SubCurrencyName As String

    ' Define currency names (change these if needed)
    CurrencyName = "Dollars"
    SubCurrencyName = "Cents"

    ReDim Place(9) As String
    Place(2) = " Thousand"
    Place(3) = " Million"
    Place(4) = " Billion"
    Place(5) = " Trillion"

    ' Convert MyNumber to string and trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find Position of decimal place (0 if none).
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        TempStr = " and " & ConvertTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) & " " & SubCurrencyName
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        ' Convert last 3 digits of MyNumber to text.
        If Val(Right(MyNumber, 3)) > 0 Then
            TempStr = ConvertHundreds(Right(MyNumber, 3)) & Place(Count) & IIf(Count > 1, ", ", " ") & TempStr
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    ' Remove any leading spaces or extra spaces and handle final formatting
    TempStr = Application.Trim(TempStr)
    
    ' Remove the final comma if it exists
    If Right(TempStr, 1) = "," Then
        TempStr = Left(TempStr, Len(TempStr) - 1)
    End If
    
    ' Replace the last comma with "and"
    Dim LastCommaPos As Integer
    LastCommaPos = InStrRev(TempStr, ",")
    If LastCommaPos > 0 Then
        TempStr = Left(TempStr, LastCommaPos - 1) & " and" & Mid(TempStr, LastCommaPos + 1)
    End If
    
    ' Add the currency name
    NumberToWords = TempStr & " " & CurrencyName
End Function

Function ConvertHundreds(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 & ConvertTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If

    ConvertHundreds = Result
End Function

Function ConvertTens(ByVal MyTens)
    Dim Result As String
    If Val(Left(MyTens, 1)) = 1 Then
        Select Case Val(MyTens)
            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(MyTens, 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(MyTens, 1))
    End If
    ConvertTens = Result
End Function

Function GetDigit(ByVal MyDigit)
    Select Case Val(MyDigit)
        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

Example:

Enter a number in a cell (e.g., 1234 in cell A1).
In another cell, type the formula:=NumberToWords(A1)
Output: One Thousand Two Hundred Thirty-Four Dollars

VBA to convert Decimal Numbers
(With Currency Format) to words

This script converts decimal numbers into words, including both currency and sub-currency (e.g., 1234.56 becomes One Thousand Two Hundred Thirty-Four Dollars and Fifty-Six Cents).

VBA Code

Function NumberToWords(ByVal MyNumber)
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim CurrencyName As String
    Dim SubCurrencyName As String

    ' Define currency names (change these if needed)
    CurrencyName = "Dollars"
    SubCurrencyName = "Cents"

    ReDim Place(9) As String
    Place(2) = " Thousand"
    Place(3) = " Million"
    Place(4) = " Billion"
    Place(5) = " Trillion"

    ' Convert MyNumber to string and trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find position of decimal place (0 if none).
    DecimalPlace = InStr(MyNumber, ".")

    ' Process decimal and integer parts
    If DecimalPlace > 0 Then
        ' Process the decimal part (sub-currency)
        TempStr = ConvertIntegerPart(Left(MyNumber, DecimalPlace - 1)) & " " & CurrencyName
        TempStr = TempStr & " and " & ConvertDecimalPart(Mid(MyNumber, DecimalPlace + 1)) & " " & SubCurrencyName
    Else
        ' No decimal part
        TempStr = ConvertIntegerPart(MyNumber) & " " & CurrencyName
    End If

    ' Final output
    NumberToWords = Application.Trim(TempStr)
End Function

Function ConvertIntegerPart(ByVal MyNumber)
    Dim Place(9) As String
    Dim TempStr As String
    Dim Count As Integer

    Place(2) = " Thousand"
    Place(3) = " Million"
    Place(4) = " Billion"
    Place(5) = " Trillion"

    Count = 1
    Do While MyNumber <> ""
        ' Convert last 3 digits of MyNumber to text.
        If Val(Right(MyNumber, 3)) > 0 Then
            TempStr = ConvertHundreds(Right(MyNumber, 3)) & Place(Count) & IIf(Count > 1, ", ", " ") & TempStr
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    ' Trim final result
    ConvertIntegerPart = Application.Trim(TempStr)
End Function

Function ConvertDecimalPart(ByVal MyDecimal)
    Dim i As Integer
    Dim Result As String
    For i = 1 To Len(MyDecimal)
        Result = Result & GetDigit(Mid(MyDecimal, i, 1)) & " "
    Next i
    ConvertDecimalPart = Application.Trim(Result)
End Function

Function ConvertHundreds(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 & ConvertTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If

    ConvertHundreds = Result
End Function

Function ConvertTens(ByVal MyTens)
    Dim Result As String
    If Val(Left(MyTens, 1)) = 1 Then
        Select Case Val(MyTens)
            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(MyTens, 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(MyTens, 1))
    End If
    ConvertTens = Result
End Function

Function GetDigit(ByVal MyDigit)
    Select Case Val(MyDigit)
        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 0: GetDigit = "Zero"
        Case Else: GetDigit = ""
    End Select
End Function

Example:

Enter a number in a cell (e.g., 1234.56 in cell A1).
In another cell, type the formula:=NumberToWords(A1)
Output: One Thousand Two Hundred Thirty-Four Dollars and Fifty-Six Cents

Using the Custom Function

Enter the Formula in Excel:
- Now you can use the NumberToWords function in any cell.
- For example, if you want to convert the number in cell A1 to words, you would enter:

=NumberToWords(A1)

Customizing Currency

To change the currency from dollars and cents to another currency, such as pounds and pence, modify these lines in the NumberToWords function:

    ' Define currency names (change these if needed)
    CurrencyName = "Pounds"
    SubCurrencyName = "Pence"

Thank you for visiting and exploring these VBA scripts for converting numbers to words. I hope you found them helpful and easy to implement. If you enjoyed this guide, feel free to share it with your friends, family, and colleagues. Your support helps me continue to provide useful resources like this one. Let me know in the comments if you have any questions or suggestions! I am always happy to help!