Formula for convert number to words

Excel unfortunately doesn't have a built-in formula to convert numbers to words (like “123” → “One Hundred Twenty-Three”). However, you can achieve this by using a VBA user-defined function (UDF) Step-by-step: Add "Number to Words" function

1. Open VBA editor

Press Alt + F11 → click Insert > Module

2. Paste the following VBA code:

Function NumberToWords(ByVal MyNumber)
    Dim Units As Variant, Tens As Variant
    Dim Temp As String, DecimalPlace As Integer, Count As Integer
    Dim DecimalPart As String

    Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _
                  "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", _
                  "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

    MyNumber = Trim(Str(MyNumber))
    If MyNumber = "" Then
        NumberToWords = ""
        Exit Function
    End If

    ' Find decimal place
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        DecimalPart = Mid(MyNumber, DecimalPlace + 1)
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

    Count = 1
    Do While MyNumber <> ""
        Select Case Count
            Case 1: Temp = ConvertHundreds(Right(MyNumber, 3))
            Case 2: Temp = ConvertHundreds(Right(MyNumber, 3)) & " Thousand " & Temp
            Case 3: Temp = ConvertHundreds(Right(MyNumber, 3)) & " Million " & Temp
            Case 4: Temp = ConvertHundreds(Right(MyNumber, 3)) & " Billion " & Temp
        End Select
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    Temp = Application.WorksheetFunction.Trim(Temp)

    If DecimalPart <> "" Then
        Temp = Temp & " and " & DecimalPart & "/100"
    End If

    NumberToWords = Temp
End Function

Private Function ConvertHundreds(ByVal MyNumber)
    Dim Units As Variant, Tens As Variant, Result As String

    Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _
                  "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", _
                  "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

    MyNumber = Right("000" & MyNumber, 3)

    If Val(Left(MyNumber, 1)) > 0 Then
        Result = Units(Val(Left(MyNumber, 1))) & " Hundred "
    End If

    If Val(Right(MyNumber, 2)) < 20 Then
        Result = Result & Units(Val(Right(MyNumber, 2)))
    Else
        Result = Result & Tens(Val(Mid(MyNumber, 2, 1)))
        If Val(Right(MyNumber, 1)) > 0 Then
            Result = Result & "-" & Units(Val(Right(MyNumber, 1)))
        End If
    End If

    ConvertHundreds = Trim(Result)
End Function

3. Save your workbook as a Macro-Enabled Workbook (.xlsm)
4. Use it in Excel

In any cell, type:

=NumberToWords(A1)

If A1 = 12345.67, the result will be:

Twelve Thousand Three Hundred Forty-Five and 67/100

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hi, the function is not working for currency with decimal ending 0. The 50 centavos result is 5/100. please help how to correct.

Thank you!