SpellNumber function for Percentages?

[karixana]

I am trying to convert three decimal place percentages into words.
I have the script for converting currency (two decimal places), but I'm new
to scripting and unsure how to modify the script.

Ex: 0.983% = Nine hundred eighty three thousandths of one percent
Ex: 1.1% = One and one tenths of one percent

Any suggestions or sample scripts would be very much appreciated!!!

Thanks!!

 

[Héctor Miguel]

hi, !

I am trying to convert three decimal place percentages into words.
I have the script for converting currency (two decimal places)
but I'm new to scripting and unsure how to modify the script.

Ex: 0.983% = Nine hundred eighty three thousandths of one percent
Ex: 1.1% = One and one tenths of one percent

Any suggestions or sample scripts would be very much appreciated!!!

assuming your data (% number-format) in [B2]

1) use the integer part to pass as argument to your script, ie. =int(b2*100)

2) use a "helper cell/column" to pass the %-fraction (same as integer part)
ie: 0.983% as -> 983 and/or 1.1% as -> 1
=mid(round(b2*100-int(b2*100),3),search(".",b2*100-int(b2*100))+1,3)

3) could it be useful if you expose your script -?-

hth,
hector.

 

[karixana]

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!

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim 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
SpellNumber = 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

hi, !

I am trying to convert three decimal place percentages into words.
I have the script for converting currency (two decimal places)
but I'm new to scripting and unsure how to modify the script.

Ex: 0.983% = Nine hundred eighty three thousandths of one percent
Ex: 1.1% = One and one tenths of one percent

Any suggestions or sample scripts would be very much appreciated!!!

assuming your data (% number-format) in [B2]

1) use the integer part to pass as argument to your script, ie. =int(b2*100)

2) use a "helper cell/column" to pass the %-fraction (same as integer part)
ie: 0.983% as -> 983 and/or 1.1% as -> 1
=mid(round(b2*100-int(b2*100),3),search(".",b2*100-int(b2*100))+1,3)

3) could it be useful if you expose your script -?-

hth,
hector.

 

[Héctor Miguel]

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

 

[karixana]

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";