SpellNumber Function

SpellNumber – Convert Number To Words In Excel

Ready-To-Use SpellNumber Function in Excel to convert a number to written text. We have programmed this Function for 14 different currencies.

Microsoft Office provides a general code that converts a given number to US Dollars in words. We have created a new code using this code to furnish currencies other than US dollars.

In simple terms, it displays a given number as “Two Thousand Three Hundred Forty-Five Dollars and Fifty Cents”.

Not only that, with the help of this function you can convert a given number to words in the following currencies:

  1. US Dollar
  2. Canadian Dollar
  3. Australian Dollar
  4. European Euros
  5. Great Britain Pound
  6. Japanese Yen
  7. UAE Dirham
  8. Saudi Riyal
  9. South African Rand
  10. Thai Baht
  11. Sri Lankan Rupee
  12. Indian Rupee
  13. Pakistani Rupee
  14. Bangladeshi Taka

In addition to the above, there is one bonus SpellNumber that is SpellNumber Function Without Currency.

Thus, there are a total of 3 VBA codes that this function uses. One code follows the English numbering system of millions and billions whereas the other follows the Indian number system of lakhs and crores.

To summarize, the first 11 currencies use SpellNumber Function whereas the other uses SpellNumber2 Function and uses SpellNumberWC for numbers without any currency.

You can easily navigate to your desired SpellNumber Function using the table of content given below.

Syntax – SpellNumber Function

=SpellNumber(Cell Reference, “Parameter”)

SpellNumber Function Sample File

To understand the process easily, click the button below to download the sample file:

SpellNumber

SpellNumber VBA Code


Option Explicit
'Main Function www.MSOfficeGeek.com
Function SpellNumber(ByVal MyNumber, Optional MyCurrency As String = "")
Dim Dollars, cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
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 "RY"
str_amount = "Riyal"
str_amounts = "Riyals"
str_cent = "Halala"
str_cents = "Halalas"
Case "DH"
str_amount = "Dirham"
str_amounts = "Dirhams"
str_cent = "Fil"
str_cents = "Fils"
Case "UK"
str_amount = "Pound"
str_amounts = "Pounds"
str_cent = "Penny"
str_cents = "Pence"
Case "EU"
str_amount = "Euro"
str_amounts = "Euros"
str_cent = "Cent"
str_cents = "Cents"
Case "JY"
str_amount = "Yen"
str_amounts = "Yens"
str_cent = "Sen"
str_cents = "Sens"
Case "CA"
str_amount = "Canadian Dollar"
str_amounts = "Canadian Dollars"
str_cent = "Cent"
str_cents = "Cents"
Case "AU"
str_amount = "Australian Dollar"
str_amounts = "Australian Dollars"
str_cent = "Cent"
str_cents = "Cents"
Case "SA"
str_amount = "Rand"
str_amounts = "Rands"
str_cent = "Cent"
str_cents = "Cents"
Case "TB"
str_amount = "Baht"
str_amounts = "Bahts"
str_cent = "Satang"
str_cents = "Satangs"
Case "SR"
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
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
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
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19…
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 ' If value between 20-99…
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)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
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

view raw

SpellNumber

hosted with ❤ by GitHub

To convert numbers to words in Google Sheets and OpenOffice Calc, you can use the NUMBERTEXT Add-in.

How To Insert SpellNumber Function in Excel?

In this case, please make note that this code is applicable where the currency system is similar to that of dollars. Trillion, Billions, Million, etc are the same.

To insert the SpellNumber Function follow the below-given steps.

1. Go To Developer Tab.

VBA Code

2. Click on Visual Basic.

3. In the Insert menu, click on Module.

VBA Code

4. Click on the “view raw” button at the right-hand bottom corner of the code.

5. Copy the Code and Paste it into the Module window.

VBA Code

6. Click on the Save button. Close the Module window.

7. Click on the File menu and select the “Save As” option.

VBA Code

8. Select Excel File Type as “Excel Macro-Enabled Workbook”. As the file now consists of a code is cannot be saved in general excel format. Hence, it needs to be saved in this format.

That’s it. Your spellnumber function is now inserted in this workbook. Moreover, this function will only be available in this workbook.

Thus, with this single code, you can use SpellNumber Function for US Dollar, Canadian Dollar, Australian Dollar, European Euros, Great Britain Pound, Japanese Yen, UAE Dirham, Saudi Riyal, South African Rand, Thai Baht, and Sri Lankan Rupee.

Just apply the above syntax and change the parameter according to your currency.

Spell Number Function – US Dollars

For US Dollar you don’t need to enter any parameter. Just enter the Function and the cell reference. The function displays the amount in “Dollars” and “Cents”. The Canadian and Australian Dollar also displays the amount in the same units.

In short, it displays the number as “Two Thousand Three Hundred Forty-Five Dollars and Fifty Cents”.

Formula

=SpellNumber(B5)

Result

SpellNumber US Dollars

 

Spell Number Function – Canadian Dollar

To display the numbers in Canadian dollars insert the parameter as “CA” in quote marks. If you don’t want the script to mention the word “Canadian” use the SpellNumber US Dollar formula.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Canadian Dollars and Fifty Cents”.

Formula

=SpellNumber(B16, “CA”)

Result

SpellNumber Canadian Dollars

Spell Number Function – Australian Dollars

Thus, to display the numbers in Australian dollars insert the parameter as “AU” in quote marks. Similar to Canadian SpellNumber, if you don’t want the word “Australian” use the SpellNumber US Dollar formula.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Australian Dollars and Fifty Cents”.

Formula

=SpellNumber(B27, “AU”)

Result

SpellNumber Australian Dollars

Spell Number Function – Euros

To display the numbers in Euros, insert the parameter as “AU” in quote marks along with the cell reference. The function displays the amount in “Euros” and “Cents”.

In other words, it displays the number as “Two Thousand Three Hundred Forty-Five Euros and Fifty Cents”.

Formula

=SpellNumber(B49, “EU”)

Result

SpellNumber Euros

Spell Number Function – Great Britain Pounds

For Great Britain pounds, insert the parameter as “UK” along with the cell reference. The function displays the amount in “Pounds” and “Pence”.

In other words, it displays the number as “Two Thousand Three Hundred Forty-Five Pounds and Fifty Pence”.

Formula

=SpellNumber(B38, “UK”)

Result

SpellNumber Great Britain Pounds

Spell Number Function – Japanese Yen

The Japanese Yen also uses the same number system of millions and billions. Insert the parameter as “JY” in quotation marks. The function displays the amount in “Yens” and “Sens”.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Yen and Fifty Sens”.

Formula

=SpellNumber(B60, “JY”)

Result

SpellNumber Japanese Yen

Spell Number Function – UAE Dirham

Insert the parameter as “DH” in quotation marks. The function displays the amount in “Dirhams” and “Fils”.

In short, it displays the number as “Two Thousand Three Hundred Forty-Five Dirhams and Fifty Fils”.

Formula

=SpellNumber(B71, “DH”)

Result

SpellNumber UAE Dirhams

Spell Number Function – Saudi Riyal

For “Saudi Riyal”, insert the parameter as “RY” in quotation marks. The function displays the amount in “Riyals” and “Halalas”.  As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Riyals and Fifty Halalas”.

Formula

=SpellNumber(B82, “RY”)

Result

SpellNumber Saudi Riyals

Spell Number Function – South African Rand

For South African Rand, insert the parameter as “SA” in quotation marks. The function displays the amount in “Rands” and “Cents”.

In other words, it displays the number as “Two Thousand Three Hundred Forty-Five Rands and Fifty Cents”.

Formula

=SpellNumber(B93, “SA”)

Result

SpellNumber South African Rands

Spell Number Function – Thai Baht

Insert the parameter as “TB” in quotation marks. The function displays the amount in “Bahts” and “Satangs”.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Bahts and Fifty Satangs”.

Formula

=SpellNumber(B104, “TB”)

Result

SpellNumber Thai Baht

Spell Number Function – Sri Lankan Rupee

The Sri Lankan Rupee also uses the same number system of millions and billions. Insert the parameter as “SR” in quotation marks. The function displays the amount in “Rupees” and “Sinhalas”.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Rupees and Fifty Sinhalas”.

Formula

=SpellNumber(B115, “SR”)

Result

SpellNumber Sri Lankan Rupees

SpellNumber For Currencies Using Lakhs and Crores

We have to use another VBA code for SpellNumber for Asian currencies. These countries include India, Pakistan, and Bangladesh.

VBA Code For SpellNumber Indian Rupees, Pakistani Rupees, and Bangladeshi Taka

You have to insert another module and copy the below VBA code:


Option Explicit
'Main Function www.MSOfficeGeek.com
Function SpellNumber2(ByVal MyNumber, Optional MyCurrency As String = "")
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count, x
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lacs "
Place(4) = " Crores "
Place(5) = " Hundred Crores "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
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 <> ""
If Count = 1 Then
x = 3
Else
x = 2
End If
Temp = GetHundreds(Right(MyNumber, x))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > x Then
MyNumber = Left(MyNumber, Len(MyNumber) – x)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Dim str_amount, str_amounts
Dim str_cent, str_cents
Select Case UCase(MyCurrency)
Case "IN"
str_amount = "Rupee"
str_amounts = "Rupees"
str_cent = "Paisa"
str_cents = "Paisas"
Case "PK"
str_amount = "Pakistani Rupee"
str_amounts = "Pakistani Rupees"
str_cent = "Paisa"
str_cents = "Paisas"
Case "BD"
str_amount = "Taka"
str_amounts = "Takas"
str_cent = "Poysha"
str_cents = "Poysha"
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
SpellNumber2 = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
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
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19…
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 ' If value between 20-99…
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)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
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

view raw

SpellNumber2

hosted with ❤ by GitHub

Spell Number Function – Indian Rupee

Insert the parameter as “IN” in quotation marks. The function displays the amount in “Rupees” and “Paisas”.

In other words, it displays the number as “Two Thousand Three Hundred Forty-Five Rupees and Fifty Paisas”.

Formula

=SpellNumber2(B3, “IN”)

Result

SpellNumber Indian Rupees

Spell Number Function – Pakistani Rupee

Insert the parameter as “PK” in quotation marks. Similar to the Indian Rupees it displays the amount in “Pakistani Rupees” and “Paisas”.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Pakistani Rupees and Fifty Paisas”.

Formula

=SpellNumber2(B16, “PK”)

Result

SpellNumber Pakistani Rupees

Spell Number Function – Bangladeshi Taka

For Bangladeshi Taka, insert the parameter as “BD” in quotation marks. The function displays the amount in “Takas” and “Poyshas”.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five Taka and Fifty Poysha”.

Formula

=SpellNumber2(B27, “BD”)

Result

SpellNumber Bangladeshi Taka

Spell Number Function Without Currency

If you require to display the number in English words without currency then you can use this SpellNumbeWC function.

VBA Code For SpellNumber Without Currency

Insert the below-given code in a new module:


Option Explicit
'Main Function
Function SpellNumberWC(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 "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetDigit(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 1)) & " " & GetDigit(Left(Mid(MyNumber, DecimalPlace + 2) & _
"00", 1))
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 Cents
Case ""
Cents = ""
Case "One"
Cents = " Point One "
Case Else
Cents = " Point " & Cents & " "
End Select
SpellNumberWC = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
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
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19…
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 ' If value between 20-99…
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)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
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

In SpellNumber Without Currency Function, you don’t have to insert any parameters. Thus, just insert the function and the cell reference.

As a result, it displays the number as “Two Thousand Three Hundred Forty-Five and Fifty”.

Formula

=SpellNumber(B5)

Result

SpellNumber Without Currency

Moreover, feel free to contact us for the customization of this Function as per your requirement. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.

Furthermore, if you have any queries or suggestions please share them in the comment section below. We will be more than happy to assist you.

%d bloggers like this: