It may happen that we need to display the numbers in words, for instance 1= one, 2016 = two thousand sixteen, etc.
The objective of this document is to provide a way to translate numbers into English words.
The formula described in this document supports number until 15 digits so the maximum value supported is 999999999999999.
So it’s large enough and if you need to have more digits then you can enrich the formula by adding new positions. Moreover, the formula supports positive and negative numbers.
To summarize, this Web Intelligence formula support units, hundreds, thousands, millions, billions, and trillions.
Prior to describe the formulas used in the document here are screenshots of translated numbers.
To do it we need 2 variables, but we can create more variables to simplify the formula that does the translation.
The first variable describes the units and is named [English units]. The variable is similar to a table of string where there is the corresponding word for a number. “zero” is not used because it only appears where the number is equal to 0 so there is a specific condition for it.
Here is the formula:
="#0##1# one#01# one#2# two#02# two#3# three#03# three#4# four#04# four#5# five#05# five#6# six#06# six#7# seven#07# seven#8# eight#08# eight#9# nine#09# nine#10# ten#11# eleven#12# twelve#13# thirteen#14# fourteen#15# fifteen#16# sixteen#17# seventeen#18# eighteen#19# nineteen#20# twenty#21# twenty one#22# twenty two#23# twenty three#24# twenty four#25# twenty five#26# twenty six#27# twenty seven#28# twenty eight#29# twenty nine#30# thirty#31# thirty one#32# thirty two#33# thirty three#34# thirty four#35# thirty five#36# thirty six#37# thirty seven#38# thirty eight#39# thirty nine#40# fourty#41# fourty one#42# fourty two#43# fourty three#44# fourty four#45# fourty five#46# fourty six#47# fourty seven#48# fourty eight#49# fourty nine#50# fifty#51# fifty one#52# fifty two#53# fifty three#54# fifty four#55# fifty five#56# fifty six#57# fifty seven#58# fifty eight#59# fifty nine#60# sixty#61# sixty one#62# sixty two#63# sixty three#64# sixty four#65# sixty five#66# sixty six#67# sixty seven#68# sixty eight#69# sixty nine#70# seventy#71# seventy one#72# seventy two#73# seventy three#74# seventy four#75# seventy five#76# seventy six#77# seventy seven#78# seventy eight#79# seventy nine#80# eighty#81# eighty one#82# eighty two#83# eighty three#84# eighty four#85# eighty five#86# eighty six#87# eighty seven#88# eighty eight#89# eighty nine#90# ninety#91# ninety one#92# ninety two#93# ninety three#94# ninety four#95# ninety five#96# ninety six#97# ninety seven#98# ninety eight#99# ninety nine#"
As you can notice there is separators (#) between each number and its associated word.
Now we just need the variable that does the conversion. This formula is complex so I added comments to explain what is the need of each paragraph (Main if statement).
To simplify the formula, you can create an intermediate variable and replace the following formula by the variable name you have created. This formula is used to format the number and remove the potential decimals and minus sign. The formula is used 84 times in the numbers to words translation:
FormatNumber(Abs([My Number]);"0")
[My Number] is the numeric variable to translate into words.
Now here is the formula used for the translation:
=if Length(FormatNumber(Abs([My Number]);"0")) > 15 then "*****" /* Too large */
else if FormatNumber(Abs([My Number]);"0") = "0" then "zero" /* Zero */
else (if [My Number] < 0 then "minus ")
+ Trim(Replace(Replace(Replace(Replace(Replace(Replace(
/* hundred of trillion */
(if Length(FormatNumber(Abs([My Number]);"0")) > 14 then
if Left(Right(FormatNumber(Abs([My Number]);"0");15);1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");15);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");15);1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");15);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");15);1))+2 ; Length([English units])); "#")-1 ) + " hundred"
else "")
/* trillion */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 13 then
if Left(Right(FormatNumber(Abs([My Number]);"0");14);2) <> "00" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");14);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");14);2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");14);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");14);2))+2 ; Length([English units])); "#")-1 ) + " trillion"
else " trillion"
else if Length(FormatNumber(Abs([My Number]);"0")) > 12 then
if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([English units])); "#")-1 ) + " trillion"
else "")
/* hundred of billion */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 11 then
if Left(Right(FormatNumber(Abs([My Number]);"0");12);1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2 ; Length([English units])); "#")-1 ) + " hundred"
else "")
/* billion */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 10 then
if Left(Right(FormatNumber(Abs([My Number]);"0");11);2) <> "00" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");11);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");11);2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");11);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");11);2))+2 ; Length([English units])); "#")-1 ) + " billion"
else " billion"
else if Length(FormatNumber(Abs([My Number]);"0")) > 9 then
if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([English units])); "#")-1 ) + " billion"
else "")
/* hundred of million */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 8 then
if Left(Right(FormatNumber(Abs([My Number]);"0");9);1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");9);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");9);1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");9);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");9);1))+2 ; Length([English units])); "#")-1 ) + " hundred"
else "")
/* million */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 7 then
if Left(Right(FormatNumber(Abs([My Number]);"0");8);2) <> "00" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");8);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");8);2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");8);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");8);2))+2 ; Length([English units])); "#")-1 ) + " million"
else " million"
else if Length(FormatNumber(Abs([My Number]);"0")) > 6 then
if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([English units])); "#")-1 ) + " million"
else "")
/* hundred of thousand */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 5 then
if Left(Right(FormatNumber(Abs([My Number]);"0");6);1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");6);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");6);1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");6);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");6);1))+2 ; Length([English units])); "#")-1 ) + " hundred"
else "")
/* thousand */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 4 then
if Left(Right(FormatNumber(Abs([My Number]);"0");5);2) <> "00" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");5);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");5);2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");5);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");5);2))+2 ; Length([English units])); "#")-1 ) + " thousand"
else " thousand"
else if Length(FormatNumber(Abs([My Number]);"0")) > 3 then
if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([English units])); "#")-1 ) + " thousand"
else "")
/* hundred */
+ (if Length(FormatNumber(Abs([My Number]);"0")) > 2 and Left(Right(FormatNumber(Abs([My Number]);"0");3);1) <> "0" then
Substr(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");3);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");3);1))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");3);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");3);1))+2 ; Length([English units])); "#")-1 ) + " hundred")
/* units */
+ Substr(Substr([English units]; Pos([English units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2 ; Length([English units])); "#")-1 )
;" trillion billion";" trillion");" trillion million";" trillion");" trillion thousand";" trillion");" billion million";" billion");" billion thousand";" billion");" million thousand";" million") )
As I said the formula is a bit complex but can be easily explained.
There is no performance issue. In the dataset I used there are 33413 different numbers all unique and it takes less than 2 seconds to translate all numbers.
I also extended the translation formula to manage the decimals (2 decimals maximum).
This formula can be used with currencies to display amount in words such as two thousand thirty five dollars and fourty two cents
Here is a screenshot of such translation:
Now here is the formula used for the translation. The core of the formula does not change except for the number formatting: we just need to remove all decimals.
So instead of having FormatNumber(Abs([My Number]);"0"), the new formula is FormatNumber(Truncate(Abs([My Decimal]);0);"0")
Now the translation formula has changed. I added and replaced the red part in the first part of the formula:
=if Length(FormatNumber(Truncate(Abs([My Decimal]);0);"0")) > 15 then "*****" /* Too large */
else (if [My Decimal] < 0 then "minus ")
+
else if FormatNumber(Truncate(Abs([My Decimal]);0);"0") = "0" or FormatNumber(Truncate(Abs([My Decimal]);0);"0") = "1" Then /* Zero or One */
(if FormatNumber(Truncate(Abs([My Decimal]);0);"0") = "0" Then "zero dollar" else "one dollar")
/* decimals */
+ (if Abs([My Decimal]) <> Truncate(Abs([My Decimal]);0) Then " and"
+ Substr(Substr([English units]; Pos([English units];"#" + Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) + "#") + Length(Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) + "#") + Length(Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2))+2 ; Length([English units])); "#")-1 ) + if (Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) = "01") then " cent" else " cents" else "")
else Trim(Replace(Replace(Replace(Replace(Replace(Replace( ……
And I added the new part (in red) at the end of the formula.
/* units */
+ Substr(Substr([English units]; Pos([English units];"#" + Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2) + "#") + Length(Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2) + "#") + Length(Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2))+2 ; Length([English units])); "#")-1 ) + " dollars"
/* decimals */
+ (if Abs([My Decimal]) <> Truncate(Abs([My Decimal]);0) Then " and"
+ Substr(Substr([English units]; Pos([English units];"#" + Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) + "#") + Length(Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2))+2; Length([English units]));1 ; Pos(Substr([English units]; Pos([English units];"#" + Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) + "#") + Length(Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2))+2 ; Length([English units])); "#")-1 ) + if (Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) = "01") then " cent" else " cents" else "")
...
For the decimals translation I used the same technique than the units.
In conclusion, we have now a powerful way to translate any number in English words.
To use it you just need to replace [My number] or [My decimal] by your measure
You can download the Web Intelligence report attached to that publication.
Didier MAZOUE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
6 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 |