richie wrote:Hello everyone.
I'm trying to develop a formula to interpret a two digit number and display its word representation (in spanish, by the way).
Since the numbers will develop in more than two-digit, I chose to do each digit in a different cell.
I've run into a funky problem in the last part of the formula, where it needs to check that, if the unity digit is not 1 or 2, it should choose from a list of values to display.
The actual problem is that if I add more values after "treinta" the cell stops working. Even if y add one more character to "treinta" (as in "treintax"), it will stop working.
I hope I'm being clear. Does anyone have an insight on this issue?
This is the (problematic) cell for the tenths (ten, twenty, thirty, etc), and responds to a cell named "d" where I manually input a digit.
IF(OR(d=1,d=2)=1,IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(AND(d=2,u=0),"veinte","veinti")),
CHOOSE(d-2,"treinta"))
This is the cell for the units. It reacts to a cell named "u" where I manually input a digit, and it is necessary for the arguments in cell "d" to work.
CONCAT(IF(d>1,IF(u=0,""," y "),""),IF(OR(u=0,AND(d=0,u<1),AND(d=1,u<6)),"",
CHOOSE(u,"un","dos","tres","cuatro","cinco","seis","siete","ocho","nueve")))
Thanks for any help.
Rich
Hi Rich,
I solved this problem for English language numbers (up to ninety nine billion, nine hundred ninety nine million, nine hundred ninety nine thousand, nine hundred ninety nine, the largest possible wthin AppleWorks's 11 digit precision) some time ago.
The basic steps in my technique were to:
1. Convert the number (in A2 for this example)to a text string consisting of the number and enough leading spaces to make the string 12 characters long. The result is stored in C2.
=RIGHT(" "&INT(A2),12)
Note: 12 spaces between the quotes.
2. Separate the resulting string into its individual characters, storing the characters in cells H2..J2, L2..N2, P2..R2 and T2..V2.
H2: =MID($C2,1,1) .. V2: =MID($C2,12,1)
3. Convert each of the characters to the corresponding word, using a lookup table, and placing each word into a separate cell Immediately below the character (ie. H3..V3).
H3: =IF(H2<>" ",LOOKUP(H2,$AF$2..$AF$11,$AG$2..$AG$11),"")
I3: =IF(I2<>" ",LOOKUP(I2,$AF$2..$AF$11,$AH$2..$AH$11),"")
J3: =IF(J2<>" ",LOOKUP(J2+10*(I2=1),$AF$2..$AF$21,$AI$2..$AI$21),"")
K3: =IF(OR(H2>0,I2>0,J2>0),"billion, ","")
The same four formulas are repeated for the millions and thousands periods (with the word in the last changed to "million, " then to "thousand, "), and the first three again for the hundreds, tens and units columns. Note the comma and space after the word in the last formula.
The change in the third formula is to handle numbers with a 1 in the 10s place (or 10 thousands place, etc.) such as 214.
LOOKUP table:
Column 1 (lookup values): (AF2..AF21) contains the numbers from 0 to 19 in ascending order.
Column 2 (hundreds): (AG2..AG11) contains an empty cell (zero) and the words "one hundred " to "nine hundred " (note the space following each word. There is NO space nor anything else in the zero cell.
Column 3 (tens): (AH2..AH11) Both the first (zero) and the second (one) cells are empty; the rest contain the words "twenty " through "ninety " (again followed by a single space.
Column 4 (ones and teens): (AI2..AI21) The zero cell is empty. The rest contain the words "one " through "nineteen " (each followed by a single space)
4. Concatenate the contents of cells H3 through V3, and place the result (the number in C2 translated into its word equivalent) in G3.
G3: =H3&I3&J3&K3&L3&M3&N3&O3&P3&Q3&R3&S3&T3&U3&V3
Besides whole numbers up to 11 digits, the spreadsheet I developed will handle decimals to three places. If you'd like a copy, email me directly, using the address available in my user profile. Please put AppleWorks Number-Word in the subject line, and specfy the AW 5 or AW 6 version.
Regards,
Barry