asked
how to convert number to text in ssrs
how to convert number in to words in indian format like lakhs and crores, the below code is supporting like if i have a value as 123456, it is returning as one hundred twenty three thousand four hundred fifty six rupees...but i need to return like 1 lakh twenty three thousand four hundred fifty six rupees..... Function SpellNumberInRupees (ByVal MyNumber) Dim Rupees, Piesa, Temp Dim DecimalPlace, Count Dim 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 cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Piesa = 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 = "No Rupees" Case "One" Rupees = "One Rupees" Case Else Rupees = Rupees & " Rupees" End Select Select Case Piesa Case "" 'Piesa = " and No Piesa" Case "One" Piesa = " and One Piesa" Case Else Piesa = " and " & Piesa & " Piesa" End Select SpellNumberInRupees = Rupees & Piesa End Function Function SpellNumberInDollars(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count Dim 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 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 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumberInDollars = Dollars & Cents 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
Although, it could be easy to tweak the above code, but seems too big to work on. But following may help you. At the moment it is handling upto "kharab" denomination with two decimal places, but the beauty of the code is that the bigger denomination can be handled pretty easily with just two more lines to be appended. Hope you can figure it out. Moreover, You can make a DLL for this code and can add it as a reference in SSRS OR you can convert it to VB if it is to be used as custom code. namespace NumberToWordSln { public class NumberToWordClass { public static string Number2Word(long lNumber) { string[] ones = {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ","Ten ", "Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Ninteen " }; string[] tens = { "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninty " }; //if if (lNumber == 0) return (""); if (lNumber < 0) { lNumber *= -1; } if (lNumber < 20) { return ones[lNumber - 1]; } if (lNumber ] 0) { lnumb = long.Parse(Numbr.Substring(0, decimalPlace)); paisas = Numbr.Substring(decimalPlace + 1) + "00"; paisas = paisas.Substring(0,2); paisas = Number2Word(long.Parse(paisas)); if (paisas.Trim() != "") paisas = " And " + paisas + "Paisas"; } else lnumb = long.Parse(Numbr.Trim()); } return Number2Word(lnumb) + "Rupees" + paisas + debit; }
3 People are following this question.
