Report

Convert Number to Spelling [Solved/Closed]

Ask a question Sukumar - Last answered on Aug 2, 2017 at 04:42 AM by Pat
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 
Helpful
+128
plus moins
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
Johny- Apr 23, 2011 at 05:59 AM
The above was very helpful, thanks a million Mannu
jyo- Oct 27, 2012 at 01:12 PM
thanks
....................
tushar dahale- May 11, 2017 at 08:06 AM
thanku
Reply
Pat- Aug 2, 2017 at 04:42 AM
Grateful...Helped me sort our issues big time!
Reply
Helpful
+13
plus moins
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.
bhagyashree- Jan 28, 2011 at 01:45 AM
please forward the formula to me
Helpful
+13
plus moins
hi, im Nhap, I can used already the spellNumber, but my problem is, how could it be write like this,,,

Twenty Thousand Three Hundred Fifty Five & 65/100 Pesos Only (P20,355.65)

is someone help me....
Helpful
+9
plus moins
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.
Mati- Feb 24, 2011 at 03:43 AM
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
duva- Mar 19, 2011 at 04:26 AM
hi..
thanks da..
Helpful
+8
plus moins
hi,

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

you can change the currency to any of your wish.

regards.
Helpful
+1
plus moins
Dear Sukumar
1st u go to module (alt+11 ) & than click on module1 than ctrl+h a small screen display than u type find with dollar & replace with rupees and again ctrl+h than find with cents & replace with paisa
now calculation shown us in rupees

regards
Manish
Helpful
+1
plus moins
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")
Helpful
+1
plus moins
Hello,

i have do as per your instruction but unfortunately, It's impossible... it's error.

tks.john
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Jun 1, 2010 at 05:39 AM
Are you sure you did not made any mistake John.
You can also use a very useful addon called morefunc
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

The function that will do in MoreFunc is NBTEXT
RayH- Apr 23, 2011 at 12:25 PM
Can someone tell why the subject of converting numbers to words keeps coming up? Are you all writing programs to print checks? Are you all doing the same class in school? I'm not trying to be mean I just don't understand why.

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!