Kioskea
Recherche
Ask a question Report

Convert Number to Spelling [Solved/Closed]

Sukumar - Latest answer on Oct 27, 2012 01:12PM
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
Read more 
Answer
+99
moins plus
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

Manoj- Mar 11, 2011 12:21AM
Formula for Formatting values in Indian Rupees Format. Example 8,23,09,000


[>9999999]##\,##\,##\,###;[>99999]#\,##\,###;###,###

Formula for Formatting values in Indian Rupees Format Number Format for Rupees format: FORMAT: [>9999999]##\,##\,##\,###;[>99999]#\,##\,###;###,### Example 1,23,343 Note: copy the above highlighted(yellow colour) text. select the text to format --> right click --> selct format cells then
go to "Number" tab. Choose "Custom" and paste the above
value in the "TYPE" box.
Dany- Mar 15, 2011 03:33AM
Thanks Dude...Cheers
duva- Mar 19, 2011 04:23AM
thank you..
Johny- Apr 23, 2011 05:59AM
The above was very helpful, thanks a million Mannu
jyo- Oct 27, 2012 01:12PM
thanks
....................
Answer
+10
moins plus
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 01:45AM
please forward the formula to me
Answer
+9
moins plus
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....

Answer
+8
moins plus
hi,

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

you can change the currency to any of your wish.

regards.

Answer
+8
moins plus
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 03:43AM
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 04:26AM
hi..
thanks da..
Answer
+1
moins plus
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

Answer
+1
moins plus
Hello,

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

tks.john

rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Jun 1, 2010 05:39AM
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 12:25PM
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.
Answer
+0
moins plus
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")

This document entitled « Convert Number to Spelling » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.