LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default SpellNumber function for Percentages?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how we can use spellnumber() function in a new worksheet / file? Amer Waheed Excel Worksheet Functions 1 May 5th 06 12:28 PM
spellnumber function Imran Excel Worksheet Functions 2 September 5th 05 03:42 PM
I NEED HELP with the SPELLNUMBER Function vag Excel Worksheet Functions 0 June 21st 05 08:17 AM
PLEASE HELP with the SPELLNUMBER function vag Excel Worksheet Functions 0 June 16th 05 01:33 PM
Spellnumber Norman Jones Excel Worksheet Functions 6 December 13th 04 07:21 AM


All times are GMT +1. The time now is 05:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"