TeamDesk Knowledge Base & Support

Patricio Bustos 1/22/2017 9:41:50 AM

Tks!

Jorge Solá 1/25/2017 11:08:41 AM

I fixed a couple of glitches in the above formula. Please use the following one instead:

List("",
Case(Mod(Floor([Quantity] / 100000000), 10),
0, null,

1, If(Mod(Floor([Quantity] / 1000000), 100) = 0, "CIEN", "CIENTO"),

2, "DOSCIENTOS", 3, "TRESCIENTOS", 4, "CUATROCIENTOS",
5, "QUINIENTOS", 6, "SEISCIENTOS", 7, "SETECIENTOS", 8, "OCHOCIENTOS", 9, "NOVECIENTOS")
& " ",

Case(Mod(Floor([Quantity] / 10000000), 10),
0, null, 1, null, 2, null, 3, "TREINTA", 4, "CUARENTA",
5, "CINCUENTA", 6, "SESENTA", 7, "SETENTA", 8, "OCHENTA", 9,"NOVENTA")
& "",

If(Mod(Floor([Quantity] / 10000000), 10)>2 and Mod(Floor([Quantity]/1000000),10)<>0," Y "),

If(Mod(Floor([Quantity] / 10000000), 10) = 2,
Case(Mod(Floor([Quantity] / 1000000), 10), 0, "VEINTE", 1, "VEINTIÚN", 2, "VEINTIDÓS", 3, "VEINTITRÉS", 4, "VEINTICUATRO",
5, "VEINTICINCO", 6, "VEINTISÉIS", 7, "VEINTISIETE", 8, "VEINTIOCHO", 9, "VEINTINUEVE"),

If(Mod(Floor([Quantity] / 10000000), 10) = 1,
Case(Mod(Floor([Quantity] / 1000000), 10), 0, "DIEZ", 1, "ONCE", 2, "DOCE", 3, "TRECE", 4, "CATORCE",
5, "QUINCE", 6, "DIECISÉIS", 7, "DIECISIETE", 8, "DIECIOCHO", 9, "DIECINUEVE"),

Case(Mod(Floor([Quantity] / 1000000), 10),
0, null, 1, "UN", 2, "DOS", 3, "TRES", 4, "CUATRO",
5, "CINCO", 6, "SEIS", 7, "SIETE", 8, "OCHO", 9, "NUEVE"))),

If([Quantity]>999999,If([Quantity]>1999999," MILLONES "," MILLÓN ")),

Case(Mod(Floor([Quantity] / 100000), 10),
0, null,

1, If(Mod(Floor([Quantity] / 1000), 100) = 0, "CIEN", "CIENTO"),

2, "DOSCIENTOS", 3, "TRESCIENTOS", 4, "CUATROCIENTOS",
5, "QUINIENTOS", 6, "SEISCIENTOS", 7, "SETECIENTOS", 8, "OCHOCIENTOS", 9, "NOVECIENTOS")
& " ",

Case(Mod(Floor([Quantity] / 10000), 10),
0, null, 1, null, 2, null, 3, "TREINTA", 4, "CUARENTA",
5, "CINCUENTA", 6, "SESENTA", 7, "SETENTA", 8, "OCHENTA", 9,"NOVENTA")
& "",

If(Mod(Floor([Quantity] / 10000), 10)>2 and Mod(Floor([Quantity]/ 1000),10)<>0," Y "),

If(Mod(Floor([Quantity] / 10000), 10) = 2,
Case(Mod(Floor([Quantity] / 1000), 10), 0, "VEINTE", 1, "VEINTIÚN", 2, "VEINTIDÓS", 3, "VEINTITRÉS", 4, "VEINTICUATRO",
5, "VEINTICINCO", 6, "VEINTISÉIS", 7, "VEINTISIETE", 8, "VEINTIOCHO", 9, "VEINTINUEVE"),

If(Mod(Floor([Quantity] / 10000), 10) = 1,
Case(Mod(Floor([Quantity] / 1000), 10), 0, "DIEZ", 1, "ONCE", 2, "DOCE", 3, "TRECE", 4, "CATORCE",
5, "QUINCE", 6, "DIECISÉIS", 7, "DIECISIETE", 8, "DIECIOCHO", 9, "DIECINUEVE"),

Case(Mod(Floor([Quantity] / 1000), 10),
0, null,

1, If(Mod(Floor([Quantity] / 10000), 100) = 0, "", "UN"),

2, "DOS", 3, "TRES", 4, "CUATRO",
5, "CINCO", 6, "SEIS", 7, "SIETE", 8, "OCHO", 9, "NUEVE"))),

If(Mod(Floor([Quantity] / 1000),1000) <> 0, " MIL "),

Case(Mod(Floor([Quantity] / 100), 10),
0, null,

1, If(Mod([Quantity], 100) = 0, "CIEN", "CIENTO"),

2, "DOSCIENTOS", 3, "TRESCIENTOS", 4, "CUATROCIENTOS",
5, "QUINIENTOS", 6, "SEISCIENTOS", 7, "SETECIENTOS", 8, "OCHOCIENTOS", 9, "NOVECIENTOS")
& " ",

Case(Mod(Floor([Quantity] / 10), 10),
0, null, 1, null, 2, null, 3, "TREINTA", 4, "CUARENTA",
5, "CINCUENTA", 6, "SESENTA", 7, "SETENTA", 8, "OCHENTA", 9,"NOVENTA")
& "",

If(Mod(Floor([Quantity] / 10), 10)>2 and Mod([Quantity], 10)<>0," Y "),

If(Mod(Floor([Quantity] / 10), 10) = 2,
Case(Mod([Quantity], 10), 0, "VEINTE", 1, "VEINTIÚN", 2, "VEINTIDÓS", 3, "VEINTITRÉS", 4, "VEINTICUATRO",
5, "VEINTICINCO", 6, "VEINTISÉIS", 7, "VEINTISIETE", 8, "VEINTIOCHO", 9, "VEINTINUEVE"),

If(Mod(Floor([Quantity] / 10), 10) = 1,
Case(Mod([Quantity], 10), 0, "DIEZ", 1, "ONCE", 2, "DOCE", 3, "TRECE", 4, "CATORCE",
5, "QUINCE", 6, "DIECISÉIS", 7, "DIECISIETE", 8, "DIECIOCHO", 9, "DIECINUEVE"),

Case(Mod([Quantity], 10),
0, null, 1, "UN", 2, "DOS", 3, "TRES", 4, "CUATRO",
5, "CINCO", 6, "SEIS", 7, "SIETE", 8, "OCHO", 9, "NUEVE"))),

If([Quantity] = 0, "CERO", If(Mod([Quantity], 1000000) = 0, "DE ")),

If([Quantity] = 1, " PESO", " PESOS"))

Dean Guidry 5/22/2019 5:24:39 PM

You can’t be serious. Lol

Dean Guidry 5/22/2019 5:49:36 PM

The author says it can't be used with decimals so that won't work for dollars and cents.

Jorge Solá 5/22/2019 7:33:39 PM

Hi, Dean. I prepared this formula for Colombian pesos, which don't need cents. If you need a formula to convert numbers to words in Spanish, that will also read decimals & will use "dollars" instead of "pesos", the above formula can be adapted.

I actually don't use it anymore, because when I would use it to convert to words the results of formulas, I would frequently get nesting errors (too many formulas within formulas). So I now use instead an auxiliary table that contains 1000 records, one for each number 0-999, with its equivalent in words, whichs allows me to use a much simpler formula. It works much better.

Let me know what you need & I can be of help.

Dean Guidry 5/22/2019 10:19:26 PM

Thanks Jorge: I deal with financial data and am a lawyer. In my suits where there is and error between the dollar amount and the amount in words, the amount in words will prevail. Prior to transitioning to Teamdesk I was heavily vested in Trackvia, a similar product. It had a built in type of column that converted dollars into words. I was a bit disappointed when I discovered Teamdesk did not. So perhaps we could collaborate on this.

Jorge Solá 5/23/2019 10:08:34 AM

Dean, the first basic question would be to know if you need to spell out values in English or in Spanish.

If you follow this link:

https://teamdesk.crmdesk.com/image.aspx?mode=file&id=1354

you'll see a PDF with 39 sample formulas. #3 can be used to spell out values in English. But from what I see, it only covers numbers 0-999, no decimals.

In either case, I'll be happy to help you. Write me at: info@miofienlanube.com

Dean Guidry 5/23/2019 11:10:36 AM

Jorge, it would be English.

Example: $3,450.27 1) Three Thousand Four Hundred Fifty point Twenty Seven or 2) Three Thousand Four Hundred Fifty dollars and Twenty Seven cents.

Jorge Solá 5/23/2019 11:43:20 AM

So you need decimals and numbers greater than 999. Either we can build on the formula in the PDF mentioned above, or I can help you get set up with the system that I currently use for Spanish, which uses an auxiliary table. I would recommend the second option, because it requires less processing and you can avoid nesting issues further down the line in case your documents use complex formulas to calculate the figure that then needs to be translated into words.

Write me at the email addres above & we can discuss it, or here is my phone: +57 321 7830745.

Jorge Sola Ullod 2/1/2023 5:01:51 AM

Dean, 3 years later I ended up setting up a system to spell out numbers, both in English & Spanish. It does numbers 0-999,999,999 with 2 decimals, plus negative numbers.
Let me know if you are still interested.