Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert number to intext
I am looking for a function to convert a amount (number) to text in indian
ruppees. For example, 23456789.00 would display as "Two crore thirty four lacs fifty six thousand seven hundred eighty nine ruppees & 00 paisa". Pleeeeeeze send any solutions. Thanx in advance. Ramesh goyal |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert number to intext
Perhaps start with
http://www.ozgrid.com/VBA/ValueToWords.htm their code is set up to work with USDollars, but should be adapatable for your purposes. "ramesh k. goyal - abohar" wrote: I am looking for a function to convert a amount (number) to text in indian ruppees. For example, 23456789.00 would display as "Two crore thirty four lacs fifty six thousand seven hundred eighty nine ruppees & 00 paisa". Pleeeeeeze send any solutions. Thanx in advance. Ramesh goyal |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert number to intext
I believe I've adapted the code from Ozgrid.com so that it will work for you.
See the Ozgrid page for instructions on how to insert it into your workbook and how to call it from a worksheet. Here is the modified code: Function SpellNumber(ByVal MyNumber) 'adapted from original code by ' http://www.ozgrid.com/VBA/ValueToWords.htm 'by 'J.Latham 'for use with Indian currency 'Altered/New code left justified Dim Dollars, Cents, Temp Dim DecimalPlace, Count Dim hundredsFlag As Boolean Dim digitCount As Integer 'we can't handle values greater 'than 10^10 (beyond Arawb) If MyNumber 10 ^ 11 Then SpellNumber = "Too Large to Translate" Exit Function End If ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Lakh " ' original Million Place(4) = " Crore " ' original Billion Place(5) = " Arawb " ' original Trillion ' String representation of amount. MyNumber = Trim(Str(MyNumber)) 'for this we will add ".00" if no 'decimal in the value If InStr(MyNumber, ".") = 0 Then MyNumber = MyNumber & ".00" End If 'and pad left side to get a length of 12 characters If Len(MyNumber) < 12 Then MyNumber = String(12 - Len(MyNumber), "0") & MyNumber End If ' 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 hundredsFlag = False Do While MyNumber < "" If hundredsFlag Then digitCount = 2 Else digitCount = 3 End If hundredsFlag = True ' get 2 digits after 1st pass Temp = GetHundreds(Right(MyNumber, digitCount)) If Temp < "" Then Dollars = Temp & Place(Count) _ & Dollars If Len(MyNumber) digitCount Then MyNumber = Left(MyNumber, Len(MyNumber) _ - digitCount) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Ruppees" Case "One" Dollars = "One Ruppee" Case Else Dollars = Dollars & " Ruppees" End Select Select Case Cents Case "" Cents = " and No Paisa" Case "One" Cents = " and One Paisa" Case Else Cents = " and " & Cents & " Paisa" End Select SpellNumber = Dollars & Cents End Function '******************************************* ' Converts a number from 100-999 into text * '******************************************* Function GetHundreds(ByVal MyNumber) 'from original code by ' http://www.ozgrid.com/VBA/ValueToWords.htm 'UNALTERED 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) 'from original code by ' http://www.ozgrid.com/VBA/ValueToWords.htm 'UNALTERED 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 'do nothing 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 'do nothing End Select ' Retrieve ones place. Result = Result & GetDigit _ (Right(TensText, 1)) End If GetTens = Result End Function '******************************************* ' Converts a number from 1 to 9 into text. * '******************************************* Function GetDigit(Digit) 'from original code by ' http://www.ozgrid.com/VBA/ValueToWords.htm 'UNALTERED 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 "ramesh k. goyal - abohar" wrote: I am looking for a function to convert a amount (number) to text in indian ruppees. For example, 23456789.00 would display as "Two crore thirty four lacs fifty six thousand seven hundred eighty nine ruppees & 00 paisa". Pleeeeeeze send any solutions. Thanx in advance. Ramesh goyal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to convert a number with spaces to just a number? | Excel Discussion (Misc queries) | |||
how do I convert a number to number of years, months and days | Excel Worksheet Functions | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) |