Meraz Al Nahian
The article will show you how to solve the spell number not working problem in Excel. Basically, Excel doesn’t have any built-in function to spell a number or currency on its own. But it’s important to store a number in its spelling form in the banking or stock market sector. Although Excel doesn’t have any built-in feature for this, we can apply a Macro to create a User Defined Function (UDF) which can return a number or currency in its spelling form. Let’s tune in the following sections of this article to see how to do this.
Download Practice Workbook
2 Possible Solutions If Spell Number Is Not Working in Excel
On the website of Microsoft Excel, there is a Macro which was used to get the spelling form of a number but it was for the older version of Excel. Here I have two different Macros for two different currencies: US Dollar and Indian Rupee, which will be effective in getting any spelling form for a number or currency.
1. Solving Spell Number for US Currency
We will use Microsoft Visual Basic for Applications (VBA) to create a UDF to get the spelling form of a number. Let’s go through the procedures below.
Steps:
- First, go to the Developer Tab and then select Visual Basic.
- After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.
- Now, 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 I’m not going to provide the heavy details because that will make it more complex. But I can suggest which variable you need to replace 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, respectively. 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. Just click on the Binocular icon of the editor and replace the Dollars (mn_Dollars) and Cents (mn_Cents) variables with your desired currency names.
- Now, 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.
- After that, use the Fill Handle to AutoFill the lower cells to see more examples of the number to spelling
We can also combine this UDF with other Excel functions to modify the results. Let’s say, we want the currency in capital letters and it will end with an “ONLY”. We can do this using CONCATENATE and UPPER functions.
=CONCATENATE(UPPER(SpellNumber1(B9)), " ONLY")
Thus you can solve the issue of spell number not working in Excel.
Read More: How to Spell Number Without Currency in Excel (2 Suitable Ways)
2. Solving If Spell Number Is Not Working for Indian Currency
We can also use a similar UDF that we used previously to get the spelling form of a number. Let’s go through the procedures below.
Steps:
- First, follow the steps of Method 1 to open a VBA Module.
- After that, 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
You can see that the Macro is a bit complex, so I’m not going to provide the heavy details because that will make it more complex. But you can follow the suggestions that I gave in the previous method by clicking on this link.
- Now, 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.
- After that, 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.
Read More: How to Spell Number in Rupees in Excel (With Easy Steps)
Applying Add-ins to Spell Numbers
The above procedures can be done only when you open the corresponding workbook. If you want this UDF to work on every Excel workbook on your PC, you can make an Add-in using the Macro. Let’s say, we want the numbers to be spelled in the US currency. See how we can do this.
Steps:
- First, create a new Excel workbook and copy the VBA code in Method 1 and paste it into a VBA Module of that workbook. Also, you should choose a unique name for the User Defined Function.
- Next, go to the File
- After that, select Save As >> Excel Add-in (.xlam File Extension) >> Save.
This operation saves the workbook as an Add-in.
- Now open the current workbook where you spell the number.
- Next, select File >> Options >> Add-ins >> Go.
- Thereafter, 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
We can use the following formula to get similar results that we achieved in Method 1. The main advantage is that we can use the SpellUSCurrency function in future workbooks like normal Excel built-in functions.
=SpellUSCurrency(B5)
Applying this Add-in will also save you from the trouble of copying and pasting the VBA code again and again.
Conclusion
The bottom line is, you can learn two basic tricks on how to solve the issue of spelling number not working in Excel. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
