Convert number to spelling [Solved/Closed]

-
Hello,
I Can change the Number into Spell but the problem is it does not convert in Indian Rupees.

Present Situation
=SpellNumber(58.20) = Fifty Dollar and Twenty Cents

My Request
=SpellNumber(58.20) = Fifty Rupees and Twenty Paise.

Regards,
Sukumar
See more 

5 replies

Best answer
178
Thank you
This is the Indian Currency, and not the one in the code above....
Cheers
'****************' Main Function *'****************
Function SpellNumbers(ByVal MyNumber)
    Dim Rupees, Paise, 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 Paise and set MyNumber to Rupee amount
        If DecimalPlace > 0 Then
            Paise = 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 Rupees = Temp & Place(Count) & Rupees
            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 Rupees
            Case ""
                Rupees = "No Rupees"
            Case "One"
                Rupees = "One Rupee"
            Case Else
                Rupees = Rupees & " Rupees"
        End Select
        Select Case Paise
            Case ""
                Paise = ""
            Case "One"
                Paise = " and One Paisa"
            Case Else
                Paise = " and " & Paise & " Paise"
         End Select
  SpellNumbers = Rupees & Paise
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

Say "Thank you" 178

A few words of thanks would be greatly appreciated. Add comment

CCM 6142 users have said thank you to us this month

Grateful...Helped me sort our issues big time!
Thanks it is good & easy. Lot of time was save because fot this function
darya31
Posts
2
Registration date
Tuesday December 12, 2017
Status
Member
Last seen
December 12, 2017
-
thanks a lot

pls do modify the code

rupees should come in starting like;

Rupees Two lacs two thousand..
mrwagh85
Posts
1
Registration date
Thursday March 22, 2018
Status
Member
Last seen
March 22, 2018
-
Thank you very much
13
Thank you
Hello,
I Can change the Number into Spell but the problem is it does not convert in Indian Rupees.

Present Situation
=SpellNumber(58.20) = Fifty Dollar and Twenty Cents

My Request
=SpellNumber(58.20) = Fifty Rupees and Twenty Paise.
please forward the formula to me
9
Thank you
HI All,

Just copy and paste the below function in excel.

Press Alt+F11, and paste the below function

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Paise, 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 paise and set MyNumber to Rupee amount.
If DecimalPlace > 0 Then
Paise = 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 Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "Zero Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
Rupees = Rupees & " Rupees"
End Select
Select Case Paise
Case ""
Paise = " and Zero Paise"
Case "One"
Paise = " and One Paise"
Case Else
Paise = " and " & Paise & " Paise"
End Select
SpellNumber = Rupees & Paise
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



PRESS ALT+Q to save and then exit.
Hi Siddiqui,

Really thanks for this and this function is working well but thats working only in formula contained excel not in all if I opened newly , pl advise how to provide link from one to all
hi..
thanks da..
7
Thank you
hi,

under the Home tab in excel, you will see number category.

you can change the currency to any of your wish.

regards.
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753
1
Thank you
If you are not concerned with "lacs" and crore etc and can live with million and billion etc then download MOREFUNC addin from http://download.cnet.com/Morefunc/3000-2077_4-10423159.html


then you can use it as

=NBTEXT(A1 ,,,"Rupee","Ru;pees","Paisa","Paisas")