Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number into percentile text
I have the following code to convert numbers 0-100 into
percercentile text. Can anyone help me turn it into one formula?? instead of three formula for three different cases? Thanks. Option Explicit ' Converts a number from 10,20,30 etc into text Function GetHundreds(HundText) Dim Result As String If Val(Right(HundText, 1)) = 0 Then Select Case Val(HundText) Case 10: Result = "tenth" Case 20: Result = "twentieth" Case 30: Result = "thirtieth" Case 40: Result = "fortieth" Case 50: Result = "fiftieth" Case 60: Result = "sixtieth" Case 70: Result = "seventieth" Case 80: Result = "eightieth" Case 90: Result = "ninetieth" Case 100: Result = "one hundreth" Case Else End Select 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 = "tenth" Case 11: Result = "eleventh" Case 12: Result = "twelvth" Case 13: Result = "thirteenth" Case 14: Result = "fourteenth" Case 15: Result = "fifteenth" Case 16: Result = "sixteenth" Case 17: Result = "seventeenth" Case 18: Result = "eighteenth" Case 19: Result = "nineteenth" 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 0: GetDigit = "zero" Case 1: GetDigit = "first" Case 2: GetDigit = "second" Case 3: GetDigit = "third" Case 4: GetDigit = "fourth" Case 5: GetDigit = "fifth" Case 6: GetDigit = "sixth" Case 7: GetDigit = "seventh" Case 8: GetDigit = "eighth" Case 9: GetDigit = "nineth" Case Else: GetDigit = "" End Select End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
number into percentile text
Rather than rewrite these functions, write another that decomposes a number
passed in and calls the other three as required, then returns the result. -- Bob Kilmer "N Lennox" wrote in message ... I have the following code to convert numbers 0-100 into percercentile text. Can anyone help me turn it into one formula?? instead of three formula for three different cases? Thanks. Option Explicit ' Converts a number from 10,20,30 etc into text Function GetHundreds(HundText) Dim Result As String If Val(Right(HundText, 1)) = 0 Then Select Case Val(HundText) Case 10: Result = "tenth" Case 20: Result = "twentieth" Case 30: Result = "thirtieth" Case 40: Result = "fortieth" Case 50: Result = "fiftieth" Case 60: Result = "sixtieth" Case 70: Result = "seventieth" Case 80: Result = "eightieth" Case 90: Result = "ninetieth" Case 100: Result = "one hundreth" Case Else End Select 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 = "tenth" Case 11: Result = "eleventh" Case 12: Result = "twelvth" Case 13: Result = "thirteenth" Case 14: Result = "fourteenth" Case 15: Result = "fifteenth" Case 16: Result = "sixteenth" Case 17: Result = "seventeenth" Case 18: Result = "eighteenth" Case 19: Result = "nineteenth" 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 0: GetDigit = "zero" Case 1: GetDigit = "first" Case 2: GetDigit = "second" Case 3: GetDigit = "third" Case 4: GetDigit = "fourth" Case 5: GetDigit = "fifth" Case 6: GetDigit = "sixth" Case 7: GetDigit = "seventh" Case 8: GetDigit = "eighth" Case 9: GetDigit = "nineth" Case Else: GetDigit = "" End Select End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
number into percentile text
The Quick and Dirty method is to just create a wrapper function that analyzes the number, then
calls the appropriate existing function. On Wed, 6 Aug 2003 15:22:12 -0700, "N Lennox" wrote: I have the following code to convert numbers 0-100 into percercentile text. Can anyone help me turn it into one formula?? instead of three formula for three different cases? Thanks. Option Explicit ' Converts a number from 10,20,30 etc into text Function GetHundreds(HundText) Dim Result As String If Val(Right(HundText, 1)) = 0 Then Select Case Val(HundText) Case 10: Result = "tenth" Case 20: Result = "twentieth" Case 30: Result = "thirtieth" Case 40: Result = "fortieth" Case 50: Result = "fiftieth" Case 60: Result = "sixtieth" Case 70: Result = "seventieth" Case 80: Result = "eightieth" Case 90: Result = "ninetieth" Case 100: Result = "one hundreth" Case Else End Select 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 = "tenth" Case 11: Result = "eleventh" Case 12: Result = "twelvth" Case 13: Result = "thirteenth" Case 14: Result = "fourteenth" Case 15: Result = "fifteenth" Case 16: Result = "sixteenth" Case 17: Result = "seventeenth" Case 18: Result = "eighteenth" Case 19: Result = "nineteenth" 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 f GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 0: GetDigit = "zero" Case 1: GetDigit = "first" Case 2: GetDigit = "second" Case 3: GetDigit = "third" Case 4: GetDigit = "fourth" Case 5: GetDigit = "fifth" Case 6: GetDigit = "sixth" Case 7: GetDigit = "seventh" Case 8: GetDigit = "eighth" Case 9: GetDigit = "nineth" Case Else: GetDigit = "" End Select End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentile | Excel Worksheet Functions | |||
Percentile | Excel Worksheet Functions | |||
Fina number of records between 2 percentile values | Excel Discussion (Misc queries) | |||
percentile | Excel Worksheet Functions | |||
Number value to text as percentile | Excel Programming |