Home |
Search |
Today's Posts |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Good morning!
Thank you for the suggestions! I will try that. I also found this over the weekend, in case you ever have need... //new and improved recursive version of my last attempt: function print_number_as_words2($number) { if ( !is_string($number) && !is_float($number) && !is_int($number) ) { return false; } if ( is_string($number) ) { //we know it's a string. see if there's a negative: if ( substr($number, 0, 1) == '-' ) { $number = substr($number, 1); $number = $number * -1; } } $number = strval($number); if ( $number == '0' ) { return "Zero"; } $negative = $number < 0 ? "Negative" : ""; $number = trim($number, '-'); $split_by_decimal = explode(".", $number); $decimal_string = ''; if ( count($split_by_decimal) 1 ) { $decimal_string = process_decimal($split_by_decimal[1]); } return trim(preg_replace("#\s+#", " ", $negative . " " . process_number($split_by_decimal[0]) . " " . $decimal_string)); } function process_number($number, $depth = 0) { $group_designators = array( "", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion", "Septillion", /*that's enough for now*/); $numbers = array( '1'="One", '2'="Two", '3'="Three", '4'="Four", '5'="Five", '6'="Six", '7'="Seven", '8'="Eight", '9'="Nine", '10'="Ten", '11'="Eleven", '12'="Twelve", '13'="Thirteen", '14'="Fourteen", '15'="Fifteen", '16'="Sixteen", '17'="Seventeen", '18'="Eighteen", '19'="Nineteen", '20'="Twenty", '30'="Thirty", '40'="Fourty", '50'="Fifty", '60'="Sixty", '70'="Seventy", '80'="Eighty", '90'="Ninety", ); //we already know that we have a numeric string. Process it in groups of three characters while ( strlen($number) 0 ) { if ( strlen($number) <= 3 ) { $number_to_process = $number; $number = ''; } else { $number_to_process = substr($number, strlen($number) - 3); $number = substr($number, 0, strlen($number) - 3); } if ( strlen($number_to_process) == 3 ) { $output[] = $numbers[substr($number_to_process, 0, 1)]; $output[] = "Hundred"; $number_to_process = substr($number_to_process, 1); } if ( isset($numbers[$number_to_process]) ) { $output[] = $numbers[$number_to_process]; } else { //we're dealing with a number greater than 20 and not divisible by 10: $tens = substr($number_to_process, 0, 1) . "0"; $ones = substr($number_to_process, 1, 1); $output[] = $numbers[$tens]; $output[] = $numbers[$ones]; } return process_number($number, $depth+1) . " " . implode(" ", $output) . " " . $group_designators[$depth]; } } function process_decimal($number) { $suffix = array( "Tenths", "Hundreths", "Thousandths", "Ten Thousandths", "Hundred Thousandths", "Millionths", //enough ); return " and " . process_number($number) . $suffix[strlen($number) - 1]; } echo print_number_as_words2("-19832498347.34") . "\n"; echo print_number_as_words2(14.12) . "\n"; echo print_number_as_words2(1432489723485) . "\n"; echo print_number_as_words2(10234.45645) . "\n"; echo print_number_as_words2(-10.2) . "\n"; echo print_number_as_words2("1298721498732.111111") . "\n"; "Héctor Miguel" wrote: hi, ! Here is the currency script I have, but I'm not sure how to modify it so that it translates the decimal into words because the way cents are written out is different than 3 decimal places. Again, any suggestions are welcome! based on Bernie Dietrck's Dollars UDF... - http://tinyurl.com/yaff92 I guess you might want to give a try to the following alternate coding: if any doubts (or further information)... wuould you please comment ? regards, hector. Function SpellPercent(Number As Single) As String Dim Integers As String, Fx As Integer, Lng As Byte, _ Nxt As Integer, Tmp As Integer, Closure As String If Int(Number * 100) Then Integers = hndWrite(Int(Number * 100)) & " and " Fx = Mid(Round(Number * 100 - Int(Number * 100), 3), _ InStr(Number * 100 - Int(Number * 100), ".") + 1, 3) Select Case Fx Case 1 To 9: Closure = " tenths" Case 10 To 99: Closure = " hundredths" Case 100 To 999: Closure = " thousandths" End Select Closure = Closure & " of one percent" Lng = Int(Application.Log10(Fx) / 3) For Nxt = Lng To 0 Step -1 Tmp = Int(Fx / 10 ^ (Nxt * 3)): Fx = Fx - Tmp * 10 ^ (Nxt * 3) If Tmp Then SpellPercent = SpellPercent & hndWrite(Int(Tmp)) Next SpellPercent = Application.Trim(Integers & SpellPercent & Closure) SpellPercent = UCase(Left(SpellPercent, 1)) & Mid(SpellPercent, 2) End Function Private Function hndWrite(ByVal Number As Integer) As String Dim Units, Tens, Hund As Byte, Dec As Byte, Uni As Byte Units = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", _ "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen") Tens = Array("", "ten", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety") Hund = Number \ 100: Number = Number - Hund * 100 If Hund 0 Then hndWrite = hndWrite & hndWrite(Int(Hund)) & " hundred " If Number = 20 Then Dec = Number \ 10 hndWrite = hndWrite & Tens(Dec) & " " Uni = Number - Dec * 10 hndWrite = hndWrite & Units(Uni) Else: hndWrite = hndWrite & Units(Number) & " " End If End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how we can use spellnumber() function in a new worksheet / file? | Excel Worksheet Functions | |||
spellnumber function | Excel Worksheet Functions | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
PLEASE HELP with the SPELLNUMBER function | Excel Worksheet Functions | |||
Spellnumber | Excel Worksheet Functions |