Community
Technology Blog Posts by Members
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Display numbers in Spanish words

Former Member
‎2016 Apr 25 1:08 PM
0 Likes
1,367

Display numbers in Spanish words

It may happen that we need to display the numbers in words, for instance 1= uno, 2016 = dos mil dieciséis, 358 = trescientos cincuenta y ocho, etc.

The objective of this document is to provide a way to translate numbers into Spanish words.

The formula described in this document supports number until 14 digits so the maximum value supported is 999999999999.

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, and billions.

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 [Spanish 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# uno#01# uno#2# dos#02# dos#3# tres#03# tres#4# quatro#04# quatro#5# cinco#05# cinco#6# seis#06# seis#7# siete#07# siete#8# ocho#08# ocho#9# nueve#09# nueve#10# diez#11# once#12# doce#13# trece#14# catorce#15# quince#16# dieciséis#17# diecisiete#18# dieciocho#19# diecinueve#20# vingt#21# veintiuno#22# veintidós#23# veintitrés#24# veintiquatro#25# veinticinco#26# veintiséis#27# veintisiete#28# veintiocho#29# veintinueve#30# treinta#31# treinta y uno#32# treinta y dos#33# treinta y tres#34# treinta y quatro#35# treinta y cinco#36# treinta y seis#37# treinta y siete#38# treinta y ocho#39# treinta y nueve#40# cuarenta#41# cuarenta y uno#42# cuarenta y dos#43# cuarenta y tres#44# cuarenta y quatro#45# cuarenta y cinco#46# cuarenta y seis#47# cuarenta y siete#48# cuarenta y ocho#49# cuarenta y nueve#50# cincuanta#51# cincuanta y uno#52# cincuanta y dos#53# cincuanta y tres#54# cincuanta y quatro#55# cincuanta y cinco#56# cincuanta y seis#57# cincuanta y siete#58# cincuanta y ocho#59# cincuanta y nueve#60# sesenta#61# sesenta y uno#62# sesenta y dos#63# sesenta y tres#64# sesenta y quatro#65# sesenta y cinco#66# sesenta y seis#67# sesenta y siete#68# sesenta y ocho#69# sesenta y nueve#70# setenta#71# setenta y uno#72# setenta y dos#73# setenta y tres#74# setenta y quatro#75# setenta y cinco#76# setenta y seis#77# setenta y siete#78# setenta y ocho#79# setenta y nueve#80# ochenta#81# ochenta y uno#82# ochenta y dos#83# ochenta y tres#84# ochenta y quatro#85# ochenta y cinco#86# ochenta y seis#87# ochenta y siete#88# ochenta y ocho#89# ochenta y nueve#90# ochenta y dix#91# noventa y uno#92# noventa y dos#93# noventa y tres#94# noventa y quatro#95# noventa y cinco#96# noventa y seis#97# noventa y siete#98# noventa y ocho#99# noventa y nueve#"

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 76 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")) > 12 then "*****"  /* Too large */

else if FormatNumber(Abs([My Number]);"0") = "0" then "cero"  /* Zero */

else (if [My Number] < 0 then "menos ")

+ Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Trim(

Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Trim(

/* trillion */

(if Length(FormatNumber(Abs([My Decimal]);"0")) > 13 then

            if Left(Right(FormatNumber(Abs([My Decimal]);"0");14);2) <> "00" then

              Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Decimal]);"0");14);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Decimal]);"0");14);2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Decimal]);"0");14);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Decimal]);"0");14);2))+2 ; Length([Spanish units])); "#")-1 ) + " billones" 

           else " billones"

         else if Length(FormatNumber(Abs([My Decimal]);"0")) > 12 then

            if Left(FormatNumber(Abs([My Decimal]);"0");1) = "1" then " un billón"

            else if Left(FormatNumber(Abs([My Decimal]);"0");1) <> "0" then

              Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Decimal]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Decimal]);"0");1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Decimal]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Decimal]);"0");1))+2 ; Length([Spanish units])); "#")-1 ) + " billones"

           else "")

/* hundred of billion */

+ (if Length(FormatNumber(Abs([My Number]);"0")) > 11 then

          if Left(Right(FormatNumber(Abs([My Number]);"0");12);1) = "1" then " ciento"

          else if Left(Right(FormatNumber(Abs([My Number]);"0");12);1) <> "0" then

            Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2 ; Length([Spanish units])); "#")-1 ) + " ciento"

          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([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");11);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");11);2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");11);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");11);2))+2 ; Length([Spanish units])); "#")-1 ) + " mil millones"

           else (if Abs([My Number]) >= 1001000000 then " mil millones" else " mil")

         else if Length(FormatNumber(Abs([My Number]);"0")) > 9 then

           if Left(FormatNumber(Abs([My Number]);"0");1) = "1" then (if Abs([My Number]) >= 1001000000 then " mil millones" else " mil")

           else if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then                      

             Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([Spanish units])); "#")-1 ) + " mil millones"

           else "")

/* hundred of million */

       + (if Length(FormatNumber(Abs([My Number]);"0")) > 8 then

           if Left(Right(FormatNumber(Abs([My Number]);"0");9);1) = "1" then if Left(Right(FormatNumber(Abs([My Number]);"0");8);2) = "00" then " cien" else " ciento"

           else if Left(Right(FormatNumber(Abs([My Number]);"0");9);1) <> "0" then

             Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");9);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");9);1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");9);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");9);1))+2 ; Length([Spanish units])); "#")-1 ) + "cientos"

           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([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");8);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");8);2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");8);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");8);2))+2 ; Length([Spanish units])); "#")-1 ) + " millones"

           else " millones"

         else if Length(FormatNumber(Abs([My Number]);"0")) > 6 then

           if Left(FormatNumber(Abs([My Number]);"0");1) = "1" then " un millón"

           else if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then

             Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([Spanish units])); "#")-1 ) + " millones"

           else "")

/* hundred of thousand */

       + (if Length(FormatNumber(Abs([My Number]);"0")) > 5 then

           if Left(Right(FormatNumber(Abs([My Number]);"0");6);1) = "1" then if Left(Right(FormatNumber(Abs([My Number]);"0");5);2) = "00" then " cien" else " ciento"

           else if Left(Right(FormatNumber(Abs([My Number]);"0");6);1) <> "0" then

             Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");6);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");6);1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");6);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");6);1))+2 ; Length([Spanish units])); "#")-1 ) + "cientos"

           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([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");5);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");5);2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");5);2) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");5);2))+2 ; Length([Spanish units])); "#")-1 ) + " mil"

           else " mil"

         else if Length(FormatNumber(Abs([My Number]);"0")) > 3 then

           if Left(FormatNumber(Abs([My Number]);"0");1) = "1" then " mil"

           else if Left(FormatNumber(Abs([My Number]);"0");1) <> "0" then

             Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(FormatNumber(Abs([My Number]);"0");1) + "#") + Length(Left(FormatNumber(Abs([My Number]);"0");1))+2 ; Length([Spanish units])); "#")-1 ) + " mil"

           else "")

/* hundred */

       + (if Length(FormatNumber(Abs([My Number]);"0")) > 2 then

          if Left(Right(FormatNumber(Abs([My Number]);"0");3);1) = "1" then if Right(FormatNumber(Abs([My Number]);"0");2) = "00" then " cien" else " ciento"

          else if Left(Right(FormatNumber(Abs([My Number]);"0");3);1) <> "0" then

            Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");3);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");3);1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");3);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");3);1))+2 ; Length([Spanish units])); "#")-1 ) + "cientos"

          else "")

/* units */

       + Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2 ; Length([Spanish units])); "#")-1 )

);" billón mil millones millones";" billón");" billones mil millones millones";" billones");" billón mil millones millones mil";" billón");" billones mil millones millones mil";" billones");" billón millones";" billón");" billones millones";" billones");" billón millón";" billón");" billones millón";" billones");"billón mil";" billón");" billones mil";" billones");" billón uno mil";" billón mil");" billones uno mil";" billones mil");" billón millones";" billón");" billones millones";" billones");" billón uno millones";" billón un millón");" billones uno millones";" billones un millón");" millones cien millones";" millones ciento millones");" millones uno millones";" millones un millón");" millones millones";" millones");" millón millones";" millón");" millón mil";" millón");" millones mil";" millones");" millón uno mil";" millón mil");" millones uno mil";" millones mil");"cincocientos";"quinientos") )

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 19198 different numbers all unique and it takes less than 1 second to translate all numbers.

  • The first condition is used to test if the number is less or equal to 14 digits.
    if Length(FormatNumber(Abs([My Number]);"0")) > 14 then "*****"
  • The second condition is used to test if the number is equal to 0.
    if FormatNumber(Abs([My Number]);"0") = "0" then "cero"
  • As you can see there is also 6 different Replace statements used to remove inaccurate translations.
    For instance with the number 100000, the initial translation writes “un millón mil”.
    We need to remove “mil”, it has been detected but there is no unit associated so this is redundant.
    We do the same for other combination that does not make sense.
  • Now lets’ have a look on the units detection (form 0 to 99).
    For the latest digits, the formula is:
    Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Abs([My Number]);"0");2) + "#") + Length(Right(FormatNumber(Abs([My Number]);"0");2))+2 ; Length([Spanish units])); "#")-1 )
    I highlighted in red this part that is used in other places like for thousands, millions, billions, etc.
    In the other places, the syntax is a bit different. It can be Left(Right(FormatNumber(Abs([My Number]);"0");5);2) or Left(Right(FormatNumber(Abs([My Number]);"0");5);1).

The bold red characters represent the number of digits to extract, and the bold green represent the position in the number.
This part of the formula is always the same everywhere and is used to extract a unit like eleven, thirty two or eighty six, for instance.

  • Lets’ have a look on the hundred detection (form 0 to 99).
    In that case there is only one digit to extract and the formula is:
    if Left(Right(FormatNumber(Abs([My Number]);"0");14);1) <> "0" then Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Left(Right(FormatNumber(Abs([My Number]);"0");12);1) + "#") + Length(Left(Right(FormatNumber(Abs([My Number]);"0");12);1))+2 ; Length([Spanish units])); "#")-1 ) + " ciento"
    We first test if the digit is different from 0 to generate the “hundred” word.
  • Concerning the thousands, millions, billions and trillions detection it works like the units.
    We just add a text before to check that the digit is different from 0 to generate the appropriate word. If there is 2 digits then we always generate the appropriate word because we nokw that there is more than zero thousand, zero million, etc.
    But in that case depending on the number of leading zeroes, this may lead to have a result with “million thousand” thus explaining the different
  • There are also additional subtleties for numbers in Spanish letters. As opposed to English we cannot say one before hundred or thousand when it’s only one: for instance in English we can say one hundred or one thousand in Spanish we need to say cent (for one hundred) and mille (for one thousand). The other units (from 2 to 99) work like in English.
    So there additional ReplaceLeft(FormatNumber(Abs([My Number]);"0");1) = "1"
  • Last we have also to consider the plural of words hundred and million and also that hundred is written differently if there is more than one hundred..

Display numbers with decimals in Spanish words

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 doscientos cuarenta y tres y dieciocho céntimos

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 replace and added the red part in the first part of the formula:

=if Length(FormatNumber(Abs([My Decimal]);"0")) > 14 then "*****"  /* Too large */

else (if [My Decimal] < 0 then "menos " 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 "cero euro" else "uno euro")

/* decimals */

+ (if Abs([My Decimal]) <> Truncate(Abs([My Decimal]);0) Then " y"

       + Substr(Substr([Spanish units]; Pos([Spanish 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([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish 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([Spanish units])); "#")-1 ) + if (Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) = "01") then " céntimo" else " céntimos" else "")

else Trim(Replace(Replace(...

And I added the new part (in red) at the end of the formula.

/* units */

+ Substr(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2) + "#") + Length(Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2))+2; Length([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish units];"#" + Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2) + "#") + Length(Right(FormatNumber(Truncate(Abs([My Decimal]);0);"0");2))+2 ; Length([Spanish units])); "#")-1 ) + " euros"

/* decimals */

+ (if Abs([My Decimal]) <> Truncate(Abs([My Decimal]);0) Then " y"

       + Substr(Substr([Spanish units]; Pos([Spanish 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([Spanish units]));1 ; Pos(Substr([Spanish units]; Pos([Spanish 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([Spanish units])); "#")-1 ) + if (Substr(FormatNumber(Abs([My Decimal]) - Truncate(Abs([My Decimal]);0);"#.##")+"0";2;2) = "01") then " céntimo" else " céntimos" 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 Spanish 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