How to Use Spell Number in Excel (2 Methods)

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_amount = "Canadian Dollar"
str_amounts = "Canadian Dollars"
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

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