Spelling numbers in Excel is often required for banking or marketing sectors. Although Excel does not offer any built-in function to spell numbers, it is possible to achieve this goal by creating custom user-defined functions.
In this article, we will explore everything you need to know about how to spell a number in Excel. By following this article, you can spell out numbers in different currencies as well as without currency as per your requirement. We have started this article by explaining how to spell a number without currency in Excel. Then we discussed how to spell a number in the US Dollar, Taka, and many other currencies.
Download Practice Workbook
Download this practice workbook while reading this article.
How to Spell a Number into Words in Excel
Go to the Developer tab and then select Visual Basic to open the Microsoft Visual Basic for Application window.
Then click on the Insert tab and select Module. As a result, a code window will appear where you can write VBA code.
Now paste the following code there.
Option Explicit
Function SpellNum(ByVal givenNumber)
Dim usDollars, usCents, Temp
Dim decimalPoint, Count
ReDim Position(9) As String
Position(2) = " Thousand "
Position(3) = " Million "
Position(4) = " Billion "
Position(5) = " Trillion "
givenNumber = Trim(Str(givenNumber))
decimalPoint = InStr(givenNumber, ".")
If decimalPoint > 0 Then
usCents = GetDigit(Left(Mid(givenNumber, decimalPoint + 1) & "00", 1)) _
& " " & GetDigit(Left(Mid(givenNumber, decimalPoint + 2) & "00", 1))
givenNumber = Trim(Left(givenNumber, decimalPoint - 1))
End If
Count = 1
Do While givenNumber <> ""
Temp = GetHundreds(Right(givenNumber, 3))
If Temp <> "" Then
usDollars = Temp & Position(Count) & usDollars
End If
If Len(givenNumber) > 3 Then
givenNumber = Left(givenNumber, Len(givenNumber) - 3)
Else
givenNumber = ""
End If
Count = Count + 1
Loop
Select Case usCents
Case ""
usCents = ""
Case "One"
usCents = " Point One "
Case Else
usCents = " Point " & usCents & " "
End Select
SpellNum = usDollars & usCents
End Function
Function GetHundreds(ByVal givenNumber)
Dim output As String
If Val(givenNumber) = 0 Then
Exit Function
End If
givenNumber = Right("000" & givenNumber, 3)
If Mid(givenNumber, 1, 1) <> "0" Then
output = GetDigit(Mid(givenNumber, 1, 1)) & " Hundred "
End If
If Mid(givenNumber, 2, 1) <> "0" Then
output = output & GetTens(Mid(givenNumber, 2))
Else
output = output & GetDigit(Mid(givenNumber, 3))
End If
GetHundreds = output
End Function
Function GetTens(TensText)
Dim output As String
output = ""
If Val(Left(TensText, 1)) = 1 Then
Select Case Val(TensText)
Case 10
output = "Ten"
Case 11
output = "Eleven"
Case 12
output = "Twelve"
Case 13
output = "Thirteen"
Case 14
output = "Fourteen"
Case 15
output = "Fifteen"
Case 16
output = "Sixteen"
Case 17
output = "Seventeen"
Case 18
output = "Eighteen"
Case 19
output = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2
output = "Twenty "
Case 3
output = "Thirty "
Case 4
output = "Forty "
Case 5
output = "Fifty "
Case 6
output = "Sixty "
Case 7
output = "Seventy "
Case 8
output = "Eighty "
Case 9
output = "Ninety "
Case Else
End Select
output = output & GetDigit(Right(TensText, 1))
End If
GetTens = output
End Function
Function GetDigit(number)
Select Case Val(number)
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
Go to your worksheet and type the following formula and press Enter to spell number without currency in Excel.
=SpellNum(B5)
How to Spell a Number into Words for Currency in Excel
1. Spell Number in US Dollar
Launch the VBA Editor window and write the following code given below.
Option Explicit
Function SpellNumUSD(ByVal MyNumber)
Dim Dollars, cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case cents
Case ""
cents = " and No Cents"
Case "One"
cents = " and One Cent"
Case Else
cents = " and " & cents & " Cents"
End Select
SpellNumUSD = Dollars & cents
End Function
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Functio
MyNumber = Right("000" & MyNumber, 3)
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 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
Now select cell B5 and write the following formula.
=SpellNumUSD(B5)
2. Spell Number in Taka
Go to the Microsoft VBA module and type the following formula in the module.
Function ConvertNumberToWords(ByVal num As Double) As String
Dim onesArr() As String
Dim tensArr() As String
Dim groupArr() As String
Dim Result As String
onesArr = Split("One,Two,Three,Four,Five," & _
"Six,Seven,Eight,Nine", ",")
tensArr = Split("Ten,Eleven,Twelve,Thirteen,Fourteen," & _
"Fifteen,Sixteen,Seventeen,Eighteen,Nineteen", ",")
groupArr = Split("Twenty,Thirty,Forty,Fifty," & _
"Sixty,Seventy,Eighty,Ninety", ",")
Dim taka As Long
Dim paisa As Long
taka = Fix(num)
paisa = Round((num - taka) * 100)
If taka = 0 And paisa = 0 Then
Result = "Zero Taka"
Else
If taka > 0 Then
Result = ConvertNumberToWordsHelper(taka) & " Taka"
End If
If paisa > 0 Then
Result = Result & " and " & ConvertNumberToWordsHelper(paisa) _
& " Paisa"
End If
End If
ConvertNumberToWords = Trim(Result)
End Function
Function ConvertNumberToWordsHelper(ByVal num As Long) As String
Dim onesArr() As String
Dim tensArr() As String
Dim groupArr() As String
Dim Result As String
onesArr = Split("One,Two,Three,Four,Five," & _
"Six,Seven,Eight,Nine", ",")
tensArr = Split("Ten,Eleven,Twelve,Thirteen,Fourteen," & _
"Fifteen,Sixteen,Seventeen,Eighteen,Nineteen", ",")
groupArr = Split("Twenty,Thirty,Forty,Fifty," & _
"Sixty,Seventy,Eighty,Ninety", ",")
If num = 0 Then
Result = ""
ElseIf num < 10 Then
Result = onesArr(num - 1)
ElseIf num < 20 Then
Result = tensArr(num - 10)
ElseIf num < 100 Then
If num Mod 10 = 0 Then
Result = groupArr(Int(num / 10) - 2)
Else
Result = groupArr(Int(num / 10) - 2) _
& " " & onesArr(num Mod 10 - 1)
End If
ElseIf num < 1000 Then
Result = onesArr(Int(num / 100) - 1) _
& " Hundred " & ConvertNumberToWordsHelper(num Mod 100)
ElseIf num < 1000000 Then
Result = ConvertNumberToWordsHelper(Int(num / 1000)) _
& " Thousand " & ConvertNumberToWordsHelper(num Mod 1000)
ElseIf num < 10000000 Then
Result = ConvertNumberToWordsHelper(Int(num / 100000)) _
& " Lakh " & ConvertNumberToWordsHelper(num Mod 100000)
ElseIf num < 100000000000# Then
Result = ConvertNumberToWordsHelper(Int(num / 10000000)) _
& " Crore " & ConvertNumberToWordsHelper(num Mod 10000000)
Else
Result = "Number is too large."
End If
ConvertNumberToWordsHelper = Trim(Result)
End Function
Go to your worksheet and apply the formula given below.
=ConvertNumberToWords(B5)
Press Enter and as a result, the amount of Taka in cell B5 will be converted into words.
3. Spell Number in Other Currencies
Now we will show you a custom VBA function that can be used to spell number in 11 different currencies.
- Riyal
- Dirham
- Pound
- Euro
- Yen
- Canadian Dollar
- Australian Dollar
- Rand
- Baht
- Sri Lankan Rupee
- US Dollar
Option Explicit
Function SpellNumber(ByVal MyNumber, Optional MyCurrency As String = "")
Dim Dollars, cents, Temp
Dim DecimalPlace, Count
Dim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Dim str_amount, str_amounts
Dim str_cent, str_cents
Select Case UCase(MyCurrency)
Case "RIYAL"
str_amount = "Riyal"
str_amounts = "Riyals"
str_cent = "Halala"
str_cents = "Halalas"
Case "DIRHAM"
str_amount = "Dirham"
str_amounts = "Dirhams"
str_cent = "Fil"
str_cents = "Fils"
Case "POUND"
str_amount = "Pound"
str_amounts = "Pounds"
str_cent = "Penny"
str_cents = "Pence"
Case "EURO"
str_amount = "Euro"
str_amounts = "Euros"
str_cent = "Cent"
str_cents = "Cents"
Case "YEN"
str_amount = "Yen"
str_amounts = "Yens"
str_cent = "Sen"
str_cents = "Sens"
Case "CANADIAN DOLLAR"
str_amount = "Canadian Dollar"
str_amounts = "Canadian Dollars"
str_cent = "Cent"
str_cents = "Cents"
Case "AUSTRALIAN DOLLAR"
str_amount = "Australian Dollar"
str_amounts = "Australian Dollars"
str_cent = "Cent"
str_cents = "Cents"
Case "RAND"
str_amount = "Rand"
str_amounts = "Rands"
str_cent = "Cent"
str_cents = "Cents"
Case "BAHT"
str_amount = "Baht"
str_amounts = "Bahts"
str_cent = "Satang"
str_cents = "Satangs"
Case "SRI LANKAN RUPEE"
str_amount = "Sri Lankan Rupee"
str_amounts = "Sri Lankan Rupees"
str_cent = "Cent"
str_cents = "Cents"
Case Else
str_amount = "Dollar"
str_amounts = "Dollars"
str_cent = "Cent"
str_cents = "Cents"
End Select
Select Case Dollars
Case ""
Dollars = "No " & str_amounts
Case "One"
Dollars = "One " & str_amount
Case Else
Dollars = Dollars & " " & str_amounts
End Select
Select Case cents
Case ""
cents = " and No " & str_cents
Case "One"
cents = " and One " & str_cent
Case Else
cents = " and " & cents & " " & str_cents
End Select
SpellNumber = Dollars & cents
End Function
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 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"
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 "
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
Now go to your dataset and select cell D5. Type the following formula to convert the amount of money into words.
=SpellNumber(B5,C5)
Press the Enter button and then autofill the rest of the cells to convert the amount into different currencies in words.
Things to Remember
- Make sure that the Developer tab is enabled. If the tab is not enabled, you can access the Microsoft VBA window by pressing Alt+F11.
- While using the last VBA function, if you insert the currency using the cell reference, make sure to put an inverted comma before the name of the currency, for instance, ‘Riyal.
Frequently Asked Questions
1. Does Excel Have a built-in function to spell number?
Unfortunately, Excel does not have any built-in function to spell number. Although it can easily be done by creating a user defined VBA function.
2. Can I modify the VBA code to spell out numbers in different languages?
The VBA code shown above in this article can be modified to spell out numbers in different languages. Just replace the English words with the equivalent words in your desired language.
3. Can I spell out numbers using alternative numbering systems (e.g. Roman numerals) using VBA in Excel?
You can create a user defined function in Excel VBA to spell out numbers using alternative numbering systems such as the Roman numerals. The function will depend on the specific numeric system you are using.
Conclusion
Thanks for making it this far. I hope you found this article useful. In this article, we have provided discussions on how to spell number in Excel. We have explained how you can spell number without currency. We also explored how to spell number in US Dollar, Taka and other currencies. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.
Spell Number in Excel: Knowledge Hub
<< Go Back to Excel Convert Number to Text | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!