SpellNumber is a manually created function through VBA Programming to change a number to written text. In other words, it converts a numeric value into English words with currency.
In this article, we will learn step by step how to create this SpellNumber function using the VBA Function.
For example, I have $ 2,345.50 and need to be displayed as “Dollar Two Thousand Three Hundred Forty-Five and Fifty Cents”.
The SpellNumber macro does as its name suggests. We have created a new code with minor changes in currency, calculations, etc. They all are based on the Microsoft code.
There is no direct function in Microsoft Excel to perform the above action. However, as many and many users demanded, they created and published the special VBA macro code on their website.
Steps to create the SpellNumber Function
Step 1: Start Microsoft Excel.
Step 2: Press ALT+F11 to open the Visual Basic Editor.
Step 3: On the Insert menu, click Module.
Step 4: Copy and Paste the below code into the Module sheet.
Code For SpellNumber
| Option Explicit | |
| 'Main Function www.ExcelDataPro.com | |
| Function SpellNumberEDP(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 "SAR" | |
| str_amount = "Riyal" | |
| str_amounts = "Riyals" | |
| str_cent = "Halala" | |
| str_cents = "Halalas" | |
| Case "AED" | |
| str_amount = "Dirham" | |
| str_amounts = "Dirhams" | |
| str_cent = "Fil" | |
| str_cents = "Fils" | |
| Case "GBP" | |
| str_amount = "Pound" | |
| str_amounts = "Pounds" | |
| str_cent = "Penny" | |
| str_cents = "Pence" | |
| Case "EUR" | |
| 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 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 | |
| SpellNumberEDP = 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 |
Step 5: Press Ctrl+S to save the workbook. As this workbook now contains a macro, while saving Excel will display the following message “The following features cannot be saved in macro-free workbook”. Click “No”.
You will see a new dialog. Select the “Save As” option.
From the drop-down menu select the “Save as type” as “Excel macro-enabled workbook”.
We are done with creating the function in your workbook. One thing is to be kept in mind that this function will only be available in this workbook.
As you plan to change workbook, it is necessary to paste the code for each workbook by following the above-mentioned steps.
Note: Our workbook now contains a macro. Each time you open this workbook or any other macro-enabled workbook, a security warning will appear below the ribbon. Select the “Enable this content” option and click OK.
How To Use the SpellNumber Function?
To display the given number in Dollars, write a number in a cell. Enter the following formula: =SpellNumberEDP(A1).
It will display the numbers as shown below:
We have created the code for 5 other currencies; Euro, Japanese Yen, Great Britain Pounds, Saudi Riyals and Uae Dirhams.
Unlike the above, here you need to enter two parameters. one is the SpellNumber function and second is the currency in quote marks. For USD you don’t need to put the second parameter.
The second parameter is different for each currency:
1. SpellNumber European Euro
European Euro = SpellNumberEDP(A1, “EUR”). Applying the parameter will display the numbers in words as displayed below:
Result:
For more information SpellNumber European Euro.
2. SpellNumber Japanese Yen
Japanese Yen = SpellNumberEDP(A1, “YEN”). Applying the second parameter will display the numbers as given below:
Result:
For more information SpellNumber Japanese Yen
3. SpellNumber Great Britain Pounds
Great Britain Pound = SpellNumberEDP(A1, “GBP”). Add GBP code in quote marks and it will display the pounds in words.
Result:
For more information SpellNumber Great Britain Pound
4. SpellNumber Saudi Riyal
Saudi Riyal = SpellNumberEDP(A1, “SAR”). Enter the second parameter and it shows as below:
Result:
For more information SpellNumber Saudi Riyal
5. SpellNumber UAE Dirham
UAE Dirhams = SpellNumberEDP(A1, “AED”). Put the send parameter as shown here. It will display the results as given below:
Result:
For more information SpellNumber UAE Dirham
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.
In such cases, if you want to add another currency, you need to make 5 changes as shown:
In the above code, instead of “SAR” write your own currency code.
For str_amount write your currency singular unit and for str_amounts write plural currency units. For decimal values write singular decimal values against str_cent and plural against str_cents.
If they are different again the code will be changed. For example; In Indian Currency, it is in lacs and crores instead of trillions, billions or millions. We have also given the made the code for SpellNumber for Indian Rupees.
Click on the link to get the code: SpellNumber Indian Rupees
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. I will be more than happy to assist you.
