How to spell in euro [Closed]

Report
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
Hello,
Dear sir / Madam

I apologise for my latest message. I need your help concerning the formula to spell in figures that is euro 7000. Which formula I need to insert.

Thanking you in advance

Brgds
Ravin

2 replies

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
456
Hi Ravin,

Have a look over here and change Rupees to Euros and Paise to Cents (Ctrl+a to select entire code, then Ctrl+h to open Find/Replace window):
https://ccm.net/forum/affich-118878-convert-number-to-spelling

Best regards,
Trowa
Dear Mr Trowad,

Thank you for the help but I am unable to convert in my excel.
Please find below message that I have receive
#NAME?
for example I need to use this info EURO 50000 IN MY EXCEL THE ANSWER I SHOULD GET IS FIFTY THOUSAND EURO.

Thank in advance
Rishi
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
456
Hi Rishi,

Paste the code below into a module:
Function SpellNumbers(ByVal MyNumber)
Dim EURO, CENT, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lac "
Place(4) = " Crore "
Place(5) = " Arab " ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
'Convert CENT and set MyNumber to Rupee amount
If DecimalPlace > 0 Then
    CENT = 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 Temp = GetHundreds(Right(MyNumber, 3))
    If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
    If Temp <> "" Then EURO = Temp & Place(Count) & EURO
    If Count = 1 And Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        If Count > 1 And Len(MyNumber) > 2 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 2)
        Else
            MyNumber = ""
        End If
    End If
    Count = Count + 1
Loop
Select Case EURO
    Case ""
    EURO = "No EURO"
    Case "One"
    EURO = "One Rupee"
    Case Else
    EURO = EURO & " EURO"
End Select
Select Case CENT
    Case ""
    CENT = ""
    Case "One"
    CENT = " and One Paisa"
    Case Else
    CENT = " and " & CENT & " CENT"
End Select
SpellNumbers = EURO & CENT
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


When you put "50000" in A1 then use the formula:
=spellnumbers(A1)
to get result: Fifty Thousand EURO.

The formula needs a number and not a text string. So when you put "EURO 50000" in A1 then the formula would look like:
=spellnumbers(RIGHT(A1,LEN(A1)-5))

This will remove the "EURO " part of the string leaving a number for the formula to convert.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!