Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displays the number in text. (One thousand two hundred thirty four
Please send me how to convert Rs. 1234.00 in Displays the number in text.
(One thousand two hundred thirty four INR in English text) i getting in Baht in Thai Text but i need in English. How do i get it? i am also using [=spellnumber(b14)] function but this function working only one time for using. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displays the number in text. (One thousand two hundred thirty four
'/=========================================/
'Main Function Function SpellNumber(ByVal MyNumber As String, _ Optional CurrencyName As String, _ Optional DecimalName As String) As String 'based on function from Microsoft Website: 'http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 'accurate to Sextillions... ' 999,999,999,999,999,999,999,999.99 '(because I don't know what comes after sextillion) ' 'MyNumber can either be directly entered into the funtion ' as a string or a number or a cell range such as ' SpellNumber("123,456.00") or ' SpellNumber(123456.00) or ' SpellNumber(C12) ' 'CurrencyName is an optional string parameter ' If entered, it will replace the default "Dollar". ' For example, if you enter "Peso", that name will ' appear instead of 'Dollar' ' 'DecimalName is an optional string parameter ' If entered, it will replace the default "Cent". ' For example, if you enter "Pesar", that name will ' appear instead of 'Cent' ' Dim Dollars, Cents, temp Dim DecimalPlace, Count Dim strCurrency As String, strDecimal As String Dim strNegative As String strCurrency = "Dollar" strDecimal = "Cent" strNegative = "" If Len(CurrencyName) < 0 Then strCurrency = Trim(CurrencyName) End If If Len(DecimalName) < 0 Then strDecimal = Trim(DecimalName) End If ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " Place(6) = " Quadrillion " Place(7) = " Quintillion " Place(8) = " Sextillion " ' String representation of amount because otherwise Excel ' represents large numbers using Scientific Notation ' such as 4.77874E+22 for 47,787,439,193,322,500,000,000.00 MyNumber = Format(MyNumber, "0,000.00") 'get rid of extraneous data such as '$' or ',' MyNumber = StripOut(MyNumber) 'check for negative sign If Left(MyNumber, 1) = "-" Then strNegative = "Minus " If Len(MyNumber) 1 Then MyNumber = Right(MyNumber, Len(MyNumber) - 1) End If 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 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 " & strCurrency & "s" Case "One" Dollars = "One " & strCurrency Case Else Dollars = Dollars & " " & strCurrency & "s" End Select Select Case Cents Case "" Cents = " and No " & strDecimal & "s" Case "One" Cents = " and One " & strDecimal Case Else Cents = " and " & Cents & " " & strDecimal & "s" End Select SpellNumber = strNegative & Dollars & Cents End Function '/=========================================/ ' Converts a number from 100-999 into text Private 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. Private 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. Private 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 '/=========================================/ Private Function StripOut(strNumber) As String Dim iLen As Integer, i As Integer Dim strInternationalDecimalSeparator As String Dim strBuildNumber As String iLen = Len(strNumber) If iLen = 0 Then StripOut = "" Exit Function End If strBuildNumber = "" strInternationalDecimalSeparator = _ Application.International(xlDecimalSeparator) For i = 1 To iLen Select Case Mid(strNumber, i, 1) 'test for numbers Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" strBuildNumber = strBuildNumber & Mid(strNumber, i, 1) 'test for sign and decimal separator Case "-", strInternationalDecimalSeparator strBuildNumber = strBuildNumber & Mid(strNumber, i, 1) Case Else strBuildNumber = strBuildNumber End Select Next i StripOut = strBuildNumber End Function '/=========================================/ Hope this is what you are looking for. Change 'Dollar' and 'Cent' to whatever you need. -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Ashish Patel" wrote: Please send me how to convert Rs. 1234.00 in Displays the number in text. (One thousand two hundred thirty four INR in English text) i getting in Baht in Thai Text but i need in English. How do i get it? i am also using [=spellnumber(b14)] function but this function working only one time for using. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell-How to add the number of cells containing text? = a sum | Excel Worksheet Functions | |||
HOW CAN I CONVERT NUMBER IN A CELL TO TEXT EG: 100 TO ONE HUNDRED | Excel Worksheet Functions | |||
Change number in Text Box in Excel | Excel Discussion (Misc queries) | |||
How do I convert number 123 to text One Hundred and Twenty Three? | Excel Discussion (Misc queries) | |||
Text Function with Different Formatting for Number | Excel Discussion (Misc queries) |