Spell Number Not Working in Excel: Complete Guide

Method 1 – Solving Spell Number for US Currency

Steps:

Go to the Developer Tab and then select Visual Basic.

The VBA editor will appear. Select Insert >> Module to open a VBA Module.

Type the following code in the VBA Module.

Option Explicit
Function SpellNumber1(ByVal mn_MyNumber)
Dim mn_Dollars, mn_Cents, mn_temp_value
Dim mn_decimal_place, mn_count
ReDim mn_place(9) As String
mn_place(2) = " Thousand "
mn_place(3) = " Million "
mn_place(4) = " Billion "
mn_place(5) = " Trillion "
mn_MyNumber = Trim(Str(mn_MyNumber))
mn_decimal_place = InStr(mn_MyNumber, ".")
If mn_decimal_place > 0 Then
mn_Cents = CalculateTens(Left(Mid(mn_MyNumber, mn_decimal_place + 1) & _
"00", 2))
mn_MyNumber = Trim(Left(mn_MyNumber, mn_decimal_place - 1))
End If
mn_count = 1
Do While mn_MyNumber <> ""
mn_temp_value = CalculateHundreds(Right(mn_MyNumber, 3))
If mn_temp_value <> "" Then mn_Dollars = mn_temp_value & mn_place(mn_count) & mn_Dollars
If Len(mn_MyNumber) > 3 Then
mn_MyNumber = Left(mn_MyNumber, Len(mn_MyNumber) - 3)
Else
mn_MyNumber = ""
End If
mn_count = mn_count + 1
Loop
Select Case mn_Dollars
Case ""
mn_Dollars = "Zero Dollars"
Case "One"
mn_Dollars = "One Dollar"
Case Else
mn_Dollars = mn_Dollars & " Dollars"
End Select
Select Case mn_Cents
Case ""
mn_Cents = ""
Case "One"
mn_Cents = " and One Cent"
Case Else
mn_Cents = " and " & mn_Cents & " Cents"
End Select
SpellNumber1 = mn_Dollars & mn_Cents
End Function
Function CalculateHundreds(ByVal mn_MyNumber)
    Dim mn_result As String
    If Val(mn_MyNumber) = 0 Then Exit Function
    mn_MyNumber = Right("000" & mn_MyNumber, 3)
    If Mid(mn_MyNumber, 1, 1) <> "0" Then
        mn_result = StoreDigit(Mid(mn_MyNumber, 1, 1)) & " Hundred "
    End If
    If Mid(mn_MyNumber, 2, 1) <> "0" Then
        mn_result = mn_result & CalculateTens(Mid(mn_MyNumber, 2))
    Else
        mn_result = mn_result & StoreDigit(Mid(mn_MyNumber, 3))
    End If
    CalculateHundreds = mn_result
End Function
Function CalculateTens(mn_tens)
    Dim mn_result As String
    mn_result = ""
    If Val(Left(mn_tens, 1)) = 1 Then
        Select Case Val(mn_tens)
            Case 10: mn_result = "Ten"
            Case 11: mn_result = "Eleven"
            Case 12: mn_result = "Twelve"
            Case 13: mn_result = "Thirteen"
            Case 14: mn_result = "Fourteen"
            Case 15: mn_result = "Fifteen"
            Case 16: mn_result = "Sixteen"
            Case 17: mn_result = "Seventeen"
            Case 18: mn_result = "Eighteen"
            Case 19: mn_result = "Nineteen"
            Case Else
        End Select
    Else
        Select Case Val(Left(mn_tens, 1))
            Case 2: mn_result = "Twenty "
            Case 3: mn_result = "Thirty "
            Case 4: mn_result = "Forty "
            Case 5: mn_result = "Fifty "
            Case 6: mn_result = "Sixty "
            Case 7: mn_result = "Seventy "
            Case 8: mn_result = "Eighty "
            Case 9: mn_result = "Ninety "
            Case Else
        End Select
        mn_result = mn_result & StoreDigit(Right(mn_tens, 1))
    End If
    CalculateTens = mn_result
End Function
Function StoreDigit(mn_digit)
    Select Case Val(mn_digit)
        Case 1: StoreDigit = "One"
        Case 2: StoreDigit = "Two"
        Case 3: StoreDigit = "Three"
        Case 4: StoreDigit = "Four"
        Case 5: StoreDigit = "Five"
        Case 6: StoreDigit = "Six"
        Case 7: StoreDigit = "Seven"
        Case 8: StoreDigit = "Eight"
        Case 9: StoreDigit = "Nine"
        Case Else: StoreDigit = ""
    End Select
End Function

You can see that the Macro is a bit complex, so provide the heavy details because that will make it more complex. You need to replace it with your currency name. As this Macro is for the US currency, you just need to replace the Dollars (mn_Dollars) and Cents (mn_Cents) variables with your country’s currency note and coin names. Replacing these variables could be difficult, too, if you want to do this one by one. The easiest process could be using the Find & Replace feature of the VBA editor. Click on the Binocular icon of the editor and replace the Dollars (mn_Dollars) and Cents (mn_Cents) variables with your desired currency names.

Go back to your sheet, type the following formula in cell C5, and press ENTER.

=SpellNumber1(B5)

The UDF name here is SpellNumber1 and will return the number in B5 as US currency.

Use the Fill Handle to AutoFill the lower cells to see more examples of the number to spelling.

We can combine this UDF with other Excel functions to modify the results. The currency is in capital letters, and it will end with an “ONLY”. Do this using the CONCATENATE and UPPER functions.

=CONCATENATE(UPPER(SpellNumber1(B9)), " ONLY")

Thus you can solve the issue of spell number not working in Excel.

Method 2 – Solving If Spell Number Is Not Working for Indian Currency

Steps:

Follow the steps of Method 1 to open a VBA Module.
Type the following code in the VBA Module.

Function SpellNumber2(mn_currency As Variant) As Variant
Dim mnFigure As Variant
Dim mn_words(19) As String
Dim mn_decimaltens(9) As String
mn_words(1) = "One"
mn_words(2) = "Two"
mn_words(3) = "Three"
mn_words(4) = "Four"
mn_words(5) = "Five"
mn_words(6) = "Six"
mn_words(7) = "Seven"
mn_words(8) = "Eight"
mn_words(9) = "Nine"
mn_words(10) = "Ten"
mn_words(11) = "Eleven"
mn_words(12) = "Twelve"
mn_words(13) = "Thirteen"
mn_words(14) = "Fourteen"
mn_words(15) = "Fifteen"
mn_words(16) = "Sixteen"
mn_words(17) = "Seventeen"
mn_words(18) = "Eighteen"
mn_words(19) = "Nineteen"
mn_decimaltens(2) = "Twenty "
mn_decimaltens(3) = "Thirty "
mn_decimaltens(4) = "Fourty "
mn_decimaltens(5) = "Fifty "
mn_decimaltens(6) = "Sixty "
mn_decimaltens(7) = "Seventy "
mn_decimaltens(8) = "Eighty "
mn_decimaltens(9) = "Ninety "
mnFigure = mn_currency
mnFigure = Format(mnFigure, "FIXED")
mn_length_figure = Len(mnFigure)
If mn_length_figure < 12 Then
mnFigure = Space(12 - mn_length_figure) & mnFigure
End If
If Val(Left(mnFigure, 9)) > 1 Then
SpellNumber2 = "Rupees "
ElseIf Val(Left(mnFigure, 9)) = 1 Then
SpellNumber2 = "Rupee "
End If
For mn_k = 1 To 3
If Val(Left(mnFigure, 2)) < 20 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & mn_words(Val(Left(mnFigure, 2)))
ElseIf Val(Left(mnFigure, 2)) > 19 Then
SpellNumber2 = SpellNumber2 & mn_decimaltens(Val(Left(mnFigure, 1)))
SpellNumber2 = SpellNumber2 & mn_words(Val(Right(Left(mnFigure, 2), 1)))
End If
If mn_k = 1 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & " Crore "
ElseIf mn_k = 2 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & " Lakh "
ElseIf mn_k = 3 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & " Thousand "
End If
mnFigure = Mid(mnFigure, 3)
Next mn_k
If Val(Left(mnFigure, 1)) > 0 Then
SpellNumber2 = SpellNumber2 & mn_words(Val(Left(mnFigure, 1))) + " Hundred "
End If
mnFigure = Mid(mnFigure, 2)
If Val(Left(mnFigure, 2)) < 20 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & mn_words(Val(Left(mnFigure, 2)))
ElseIf Val(Left(mnFigure, 2)) > 19 Then
SpellNumber2 = SpellNumber2 & mn_decimaltens(Val(Left(mnFigure, 1)))
SpellNumber2 = SpellNumber2 & mn_words(Val(Right(Left(mnFigure, 2), 1)))
End If
mnFigure = Mid(mnFigure, 4)
If Val(mnFigure) > 0 Then
SpellNumber2 = SpellNumber2 & " Paise "
If Val(Left(mnFigure, 2)) < 20 And Val(Left(mnFigure, 2)) > 0 Then
SpellNumber2 = SpellNumber2 & mn_words(Val(Left(mnFigure, 2)))
ElseIf Val(Left(mnFigure, 2)) > 19 Then
SpellNumber2 = SpellNumber2 & mn_decimaltens(Val(Left(mnFigure, 1)))
SpellNumber2 = SpellNumber2 & mn_words(Val(Right(Left(mnFigure, 2), 1)))
End If
End If
mnFigure = mn_currency
mnFigure = Format(mnFigure, "FIXED")
If Val(mnFigure) > 0 Then
SpellNumber2 = SpellNumber2 & " Only "
End If
End Function

The Macro is a bit complex, so I won’t provide the heavy details because that will make it more complex. Follow the suggestions that gave in the previous method by clicking on this link.

Go back to your sheet, type the following formula in cell C5, and press ENTER.

=SpellNumber2(B5)

The UDF name here is SpellNumber2 and will return the number in B5 as Indian currency.

Use the Fill Handle to AutoFill the lower cells to see more examples of the number to spelling.

Thus you can solve the issue of spell number not working in Excel.

Applying Add-ins to Spell Numbers

Steps:

Create a new Excel workbook to copy the VBA code in Method 1, and paste it into a VBA Module of that workbook. Choose a unique name for the User Defined Function.
Go to the File.

Select Save As >> Excel Add-in (.xlam File Extension) >> Save.

This operation saves the workbook as an Add-in.

Open the current workbook where you spell the number.
Select File >> Options >> Add-ins >> Go.

The Add-ins window will show up. Check Spelluscurrency, as it is the Add-in that we can use to convert numbers or currencies to their spelling.

Use the following formula to get similar results achieved in Method 1. The main advantage is using the SpellUSCurrency function in future workbooks like normal Excel built-in functions.

=SpellUSCurrency(B5)