# How to Use Spell Number in Excel (2 Methods)

## Method 1 – Using Spell Number with Currency in Excel

VBA code is used to create the Spell Number function which will convert the numbers and currency symbol of a given cell into text.

Steps

• Go to the Developer tab on the ribbon.
• Select the Visual Basic option from the CodeÂ group.

• It will open the Visual BasicÂ window.
• Go to the Insert tab at the top.
• Select the ModuleÂ option.

• A Module code window will appear.
• Enter the following code.
``````Option Explicit
Function SpellNumberX(ByVal MyNum, Optional MyCurr As String = "")
Dim Dllr, Cts, Temp
Dim DeciPlace, CountX
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNum = Trim(Str(MyNum))
DeciPlace = InStr(MyNum, ".")
If DeciPlace > 0 Then
Cts = GetTens(Left(Mid(MyNum, DeciPlace + 1) & _
Â Â Â Â Â Â Â  "00", 2))
MyNum = Trim(Left(MyNum, DeciPlace - 1))
End If
CountX = 1
Do While MyNum <> ""
Temp = GetHundreds(Right(MyNum, 3))
If Temp <> "" Then Dllr = Temp & Place(CountX) & Dllr
If Len(MyNum) > 3 Then
MyNum = Left(MyNum, Len(MyNum) - 3)
Else
MyNum = ""
End If
CountX = CountX + 1
Loop
Dim str_amount, str_amounts
Dim str_cent, str_Cts
Select Case UCase(MyCurr)
Case "EU"
str_amount = "Euro"
str_amounts = "Euros"
str_cent = "Cent"
str_Cts = "Cents"
Case "CA"
str_cent = "Cent"
str_Cts = "Cents"
Case "AU"
str_amount = "Australian Dollar"
str_amounts = "Australian Dollars"
str_cent = "Cent"
str_Cts = "Cents"
Case Else:
str_amount = "Dollar"
str_amounts = "Dollars"
str_cent = "Cent"
str_Cts = "Cents"
End Select
Select Case Dllr
Case ""
Dllr = "No " & str_amounts
Case "One"
Dllr = "One " & str_amount
Case Else
Dllr = Dllr & " " & str_amounts
End Select
Select Case Cts
Case ""
Cts = " and No " & str_Cts
Case "One"
Cts = " and One " & str_cent
Case Else
Cts = " and " & Cts & " " & str_Cts
End Select
SpellNumberX = Dllr & Cts
End Function
Function GetHundreds(ByVal MyNum)
Dim Result As String
If Val(MyNum) = 0 Then Exit Function
MyNum = Right("000" & MyNum, 3)
If Mid(MyNum, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNum, 1, 1)) & " Hundred "
End If
If Mid(MyNum, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNum, 2))
Else
Result = Result & GetDigit(Mid(MyNum, 3))
End If
GetHundreds = Result
End Function
Function GetTens(TensText)
Dim Result As String
Result = ""
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``````
• Then, close the Visual Basic

Spell Number for Dollars

First of all, we would like to show the spell number with currency for dollars. To do this, you need to follow the previous steps carefully. Then, follow the steps.

• First, select the cell where you want to use the spell number function.
• Then, write down the following formula.
`=SpellNumberX(B5)`

• Press Enter to apply the formula.

• Drag the Fill Handle icon to fill the column.

Spell Number for Euros

To spell the number and define it as Euros rather than Dollars, we need to define the Euros as â€˜EUâ€™.

• Select the cell where you want to use the spell number function.
• Write down the following formula.
`=SpellNumberX(B5,"EU")`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

Spell Number for Australian Dollars

• Select the cell where you want to use the spell number function.
• Write down the following formula.
`=SpellNumberX(B5,"AU")`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Select the cell where you want to use the spell number function.
• Write down the following formula.
`=SpellNumberX(B5,"CA")`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

VBA Code Explanation:

``````Option Explicit
Function SpellNumberX(ByVal MyNum, Optional MyCurr As String = "")``````

Defines the function name and also defines the value of the function.

``````Dim Dllr, Cts, Temp
Dim DeciPlace, CountX
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "``````

Declares the necessary variable for the macro. The String is a representation of the amount.

``Â MyNum = Trim(Str(MyNum))``

Trims the string valueâ€™s extra space.

``DeciPlace = InStr(MyNum, ".")``

Uses the InStr function to find the position of the sub-string within a string and set the position of the decimal place as 0 if none.

``````If DeciPlace > 0 Then
Cts = GetTens(Left(Mid(MyNum, DeciPlace + 1) & _
Â "00", 2))
MyNum = Trim(Left(MyNum, DeciPlace - 1))
End If
CountX = 1``````

If the decimal place is greater than zero the formula convert Cents and set Numbers to the dollar amount. If the criteria match then CountX = 1

``````Do While MyNum <> ""
Temp = GetHundreds(Right(MyNum, 3))
If Temp <> "" Then Dllr = Temp & Place(CountX) & Dllr
If Len(MyNum) > 3 Then
MyNum = Left(MyNum, Len(MyNum) - 3)
Else
MyNum = ""
End If
CountX = CountX + 1
Loop``````

Uses the Do while loop, if the number is not blank, then checks if the temp is not blank. After that, defines the dollar. Checks if the number is greater than 3 then defines the number value. Otherwise, it will return a blank. If the loop works, then it adds the 1 to the previous countX and finish the loop.

``````Dim str_amount, str_amounts
Dim str_cent, str_Cts``````

Declares the necessary variable for the macro.

``````Select Case UCase(MyCurr)
Case "EU"
str_amount = "Euro"
str_amounts = "Euros"
str_cent = "Cent"
str_Cts = "Cents"``````

Defines the case for the Euro currency.

``````Case "CA"
str_cent = "Cent"
str_Cts = "Cents"``````

Defines the case for the Canadian currency.

``````Case "AU"
str_amount = "Australian Dollar"
str_amounts = "Australian Dollars"
str_cent = "Cent"
str_Cts = "Cents"``````

Defines the case for the Australian currency.

``````Â Case Else:
Â str_amount = "Dollar"
Â str_amounts = "Dollars"
Â str_cent = "Cent"
Â str_Cts = "Cents"
Â End Select``````

If you use the value in the spell number function for anything other than those three currencies, then it will return the dollar currency. End the selection.

``````Select Case Dllr
Case ""
Dllr = "No " & str_amounts
Case "One"
Dllr = "One " & str_amount
Case Else
Dllr = Dllr & " " & str_amounts
End Select
``` Selects the dollar case. If the case is blank, then it will return No. For case one, it returns one dollar. Otherwise, it will return the dollar amounts.```
``````Select Case Cts
Case ""
Cts = " and No " & str_Cts
Case "One"
Cts = " and One " & str_cent
Case Else
Cts = " and " & Cts & " " & str_Cts
End Select``````

For cents, if the case is blank, then it will return No. For case one, it returns one dollar. Otherwise, it will return the cents amounts.

``````SpellNumberX = Dllr & Cts
End Function``````

When you use the spell function and define the numbered cell, it will return the value of both dollars and cents.

``Function GetHundreds(ByVal MyNum)``

Defines the function GetHundreds.

``Dim Result As String``

Declares the necessary variable for the macro.

``````If Val(MyNum) = 0 Then Exit Function
MyNum = Right("000" & MyNum, 3)``````

If the Val function returns a zero number from the string, then the function will end.

``````Â If Mid(MyNum, 1, 1) <> "0" Then
Â Result = GetDigit(Mid(MyNum, 1, 1)) & " Hundred "
Â End If``````

The mid function returns a specific number of characters from the string. Here, the mid function extracts the first character if this is not equal to zero then defines the result for it.

``````If Mid(MyNum, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNum, 2))
Else
Result = Result & GetDigit(Mid(MyNum, 3))
End If``````

The formula continues through each of the positions in the string.

``````GetHundreds = Result
End Function``````

The GetHundreds take the result and ends the function.

``Function GetTens(TensText)``

Defines the function GetTens for the macro.

``Dim Result As String``

Declares the necessary variable for the macro.

``Result = ""``

Defines the result as null out of the temporary function value

``````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``````

Sets the If condition. The Left function returns the first character from the TensText.

The Val function takes that returned value and returns the number that is contained in that string and checks if the number is equal to 1. If it meets the criterion, then it returns the defined case result.

``````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``````

Otherwise, If the value is between 20-99, It also goes to the case using the Val and Left function and then returns the defined case result.

``````Result = Result & GetDigit _
(Right(TensText, 1))Â
End If
GetTens = Result
End Function``````

Defines the result with GetDigit. Uses the Right function to select the right position.

``Function GetDigit(Digit)``

Converts a number from 1 to 9 into text. Defines the GetDigit function of the macro.

``````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``````

Selects the case using the Val function. Checks which case is applicable. Otherwise, it returns as blank.

``End Function``

End the function of the macro

## Method 2 – Using Spell Number Without Currency in Excel

Steps

• Go to the Developer tab on the ribbon.
• Select the Visual Basic option from the CodeÂ group.

• It will open the Visual BasicÂ window.
• Go to the Insert tab at the top.
• Select the ModuleÂ option.

• A Module code window will appear.
• Enter the following code.
``````Option Explicit
Function SpellNumberY(ByVal MyNum)
Dim Dllr, Cts, Temp
Dim DeciPlace, CountX
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNum = Trim(Str(MyNum))
DeciPlace = InStr(MyNum, ".")
If DeciPlace > 0 Then
Cts = GetDigit(Left(Mid(MyNum, DeciPlace + 1) & _
"00", 1)) & " " & GetDigit(Left(Mid(MyNum, DeciPlace + 2) & _
"00", 1))
MyNum = Trim(Left(MyNum, DeciPlace - 1))
End If
CountX = 1
Do While MyNum <> ""
Temp = GetHundreds(Right(MyNum, 3))
If Temp <> "" Then Dllr = Temp & Place(CountX) & Dllr
If Len(MyNum) > 3 Then
MyNum = Left(MyNum, Len(MyNum) - 3)
Else
MyNum = ""
End If
CountX = CountX + 1
Loop
Select Case Cts
Â Case ""
Â Â Cts = ""
Â Â Case "One"
Â Â Cts = " Point One "
Â Â Case Else
Â Cts = " Point " & Cts & " "
Â End Select
Â SpellNumberY = Dllr & Cts
End Function
Function GetHundreds(ByVal MyNum)
Dim Result As String
If Val(MyNum) = 0 Then Exit Function
Â MyNum = Right("000" & MyNum, 3)
Â If Mid(MyNum, 1, 1) <> "0" Then
Â Result = GetDigit(Mid(MyNum, 1, 1)) & " Hundred "
Â End If
Â If Mid(MyNum, 2, 1) <> "0" Then
Â Result = Result & GetTens(Mid(MyNum, 2))
Â Else
Â Result = Result & GetDigit(Mid(MyNum, 3))
Â End If
Â GetHundreds = Result
End Function
Function GetTens(TensText)
Dim Result As String
Result = ""
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``````
• Close the Visual BasicÂ window.
• Select the cell where you want to use the spell number function.
• Enter the following formula.
`=SpellNumberY(B5)`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

VBA Code Explanation:

``````Option Explicit
Function SpellNumberY(ByVal MyNum)``````

Defines the function name and also defines the value of the function.

``````Dim Dllr, Cts, Temp
Dim DeciPlace, CountX
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "``````

Declares the necessary variable for the macro. The String is a representation of the amount.

``MyNum = Trim(Str(MyNum))``

Trims the string valueâ€™s extra space.

``DeciPlace = InStr(MyNum, ".")``

Uses the InStr function to find the position of the sub-string within a string and set the position of the decimal place as 0 if none.

``````If DeciPlace > 0 Then
Cts = GetDigit(Left(Mid(MyNum, DeciPlace + 1) & _
"00", 1)) & " " & GetDigit(Left(Mid(MyNum, DeciPlace + 2) & _
"00", 1))
Â MyNum = Trim(Left(MyNum, DeciPlace - 1))
Â End If
CountX = 1``````

If the decimal place is greater than zero, converts Cents and set Numbers to the dollar amount. If the criteria matches then sets the countX as 1.

``````Do While MyNum <> ""
Temp = GetHundreds(Right(MyNum, 3))
If Temp <> "" Then Dllr = Temp & Place(CountX) & Dllr
If Len(MyNum) > 3 Then
MyNum = Left(MyNum, Len(MyNum) - 3)
Else
MyNum = ""
End If
CountX = CountX + 1
Loop``````

Uses the Do while loop, if the number is not blank.Â  Then, checks if the temp is not blank.

Defines the dollar and checks if the number is greater than 3 then defines the number value. Otherwise, it will return a blank. If the loop works then add the 1 to the previous countX and finish the loop.

``````Select Case Cts
Case ""
Cts = ""
Case "One"
Cts = " Point One "
Case Else
Cts = " Point " & Cts & " "
End Select``````

For the cents, if the case is blank, then it will return cents as blank. For case one, it returns cents as point one. Otherwise, it will return the cents amounts.

``````SpellNumberY = Dllr & Cts
End Function``````

When you use the spell function and define the numbered cell, it will return the value of both dollars and cents.

``Function GetHundreds(ByVal MyNum)``

To Convert a number from 100-999 into text, define the function of the macro.

``Dim Result As String``

Declare the necessary variable for the macro.

``````If Val(MyNum) = 0 Then Exit Function
MyNum = Right("000" & MyNum, 3)``````

If the Val function returns a zero number from the string, then the function will end.

``````If Mid(MyNum, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNum, 1, 1)) & " Hundred "
End If``````

The Mid function returns a specific number of characters from the string. Here, the mid function extracts the first character if this is not equal to zero or not, then it defines the result to convert the hundreds place.

``````Â  If Mid(MyNum, 2, 1) <> "0" Then
Â Â Result = Result & GetTens(Mid(MyNum, 2))
Â Â Else
Â Â Result = Result & GetDigit(Mid(MyNum, 3))
Â Â End If``````

The Mid function starts from 2nd place and finishes after one character, checks if that is not equal to zero or not. It defines the result with GetTens to convert the tens place, or it takes results using GetDigit to convert the one’s place.

``````GetHundreds = Result
End Function``````

GetHundreds returns the result and ends the function.

``Function GetTens(TensText)``

To convert a number from 10 to 99 into text, define the function of the macro.

``Dim Result As String``

Declare the necessary variable for the macro.

``Result = ""``

Define the result as null out of the temporary function value

``````Â  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``````

Set the If condition. The left function returns the first character from the TensText. The Val function takes that returned value and returns the number that is contained in that string. It checks if the number is equal to 1. If it meets the criterion, then it returns the defined case result and ends the process.

``````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``````

If the value is between 20-99, It also goes to the case using the Val and Left function and returns the defined case result.

``````Result = Result & GetDigit _
(Right(TensText, 1))
End If
GetTens = Result
End Function``````

Defines the result with GetDigit, uses the Right function to retrieves the correct place.

``Function GetDigit(Digit)``

Converts a number from 1 to 9 into text. Defines the GetDigit function of the macro.

``````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``````

Select the case using the Val function. It returns numbers that are contained in a string then checks which case is applicable for that. Otherwise, it returns as blank and ends the selection.

``End Function``

Ends the function of the macro

Read More: How to Spell Number in Taka in Excel

## Related Articles

<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

Sandeep Bhattacharjee May 27, 2023 at 1:47 PM

Its a wonderful experience. I am not any master or professional, I just practice excel at home. The Codes provided in the website of exceldemy.com really works without any difficulties or confusion. I am really happy and wish that I will be able to learn a lot from this website. Thank you and hope that you will continue to help us in learning.

2. i am Trying this and its work but if we close excel and opne again than this formula not work

Osman Goni Ridwan Oct 4, 2023 at 12:05 PM

Hello Ankit,
Thanks for sharing your problem. The file is working completely fine from our end. So, if you think your Excel VBA function is not working after saving and reopening the workbook, there could be several reasons for this issue. Here are some common troubleshooting steps to help you resolve the problem:

1. Check for Macro Security Settings:
>> Excel has a security feature that may disable macros by default.
>> Make sure that macros are enabled in your Excel settings.
>> Go to “File” > “Options” > “Trust Center” > “Trust Center Settings.” Under “Macro Settings,” select “Enable all macros” or “Enable macros for this session” to allow macros to run.

2. Check Workbook FIle Extension
Check if the VBA code is stored in a macro-enabled workbook (with the .xlsm file extension) rather than a regular .xlsx workbook. Macros won’t run in regular Excel workbooks.

3. Check Event Triggers:
Check that we have used the code associated with each worksheet. Make sure that these events are still properly associated with the worksheets in your file.

I believe by following these steps, you should be able to identify and resolve the issue. Please inform us of the outcome in a reply. Thanks for staying with us!

Advanced Excel Exercises with Solutions PDF