SpellNumber European Euros, a manually created function in using VBA coding to displays a given number in words with European Euros as currency.
There is no SpellNumber function in excel. Microsoft has given a code for SpellNumbers USD. You need to manually create this function in the workbook following some easy steps.
Using SpellNumber will display $1,234.50 in the form of “Dollar One Thousand Two Hundred Thirty-Four and Fifty Cents” in the destination cell.
Whereas using SPellNumber European Euros will display ¥ 1,234.50 as ” One Thousand Two Hundred Thirty-Four Yen and Fifty Sen”.
We have done minor editing in the code provided by Microsoft to display the text in Euros and Cents.
You can also check other spellnumber function like SpellNumber Japanese Yen, SpellNumber Great Britain Pound SpellNumber Without Currency, SpellNumber for UAE Dirhams, SpellNumber Saudi Riyals & SpellNumber Indian Rupees.
Step by Step process to create SpellNumber Function for European Euros is given below.
How To Create SpellNumber European Euros Function
1: Open a new Excel workbook.
2: Go to the Developer Tab.
3: Click on “Visual Basic” on the top left corner of the ribbon.
4: On “Insert” Tab, Click on “Module”. This will open a new module sheet.
5: Copy the following Code into the Module sheet.
Code for SpellNumber Function for EUR (European Euros)
| 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 |
6: Click on “Save” button or press “CTRL + S”
The screen will display the following message:
“The following features cannot be saved in macro-free workbook”.
Click “No” as you have to save the file as a macro-enabled workbook.
7: “Save as type” must be selected as “Excel macro-enabled workbook”.
SpellNumberEDP Function for Euros is now added to your function list. Please note that this function will be available in this workbook only. Follow the same steps and paste the code in another workbook.
Make sure you follow the below steps when you open any macro-enabled workbook.
A Security Warning as shown will appear below the ribbon:
Select “Enable this content” & Click “OK”. If you don’t do this any added function in excel workbook will not function properly.
How To Use Spell Number Function For European Euro?
- Insert your desired numbers in Column A which you want to convert to words in Euros.
- Select the function as =(SpellNumberEDP(cell reference), “EUR”) in column B
The second parameter is not case sensitive so you can enter either “EUR” or “eur”.
- Using Fill Handle, copy the same formula to Column B. It will display the numbers as shown below:
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries or suggestions please share in the comment section below. I will be more than happy to assist you.
