Convert number to text (10 -> Ten)
Hello,
I need to convert number to text. For example in invoces or bills i need to write the total amount for exp 100.55 To One Hundred Euros FiftyFive Cents
Convert number to text (10 -> Ten)
null-OTHER, iOS 10.2
Question marked as
Top-ranking reply
Hi OT,
Here's a table that should fit the bill.
The basic layout is from a template posted about three years ago in the iwork community forums by mswint. I've rewritten most of the formulas and expanded the range of values it can handle, and using a different process.
The number to be converted is entered in B1 (he salmon colour filled cell, and the final result is produced in cell C6 (and flows into the space holding the other (empty) cells in that row (light green filled cells).
Pale yellow filled cells are all involved in the calculation.
Columns A and B are a lookup table, listing the numbers from 0 (in A3) to 99 (in A102), and the text equivalents in column B.
For convenience, I've pasted the values here. You can simply copy them, then paste them into your own table by selecting (one click) cell A3, then pressing command-V.
0 |
|
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 |
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 |
forty |
41 |
forty-one |
42 |
forty-two |
43 |
forty-three |
44 |
forty-four |
45 |
forty-five |
46 |
forty-six |
47 |
forty-seven |
48 |
forty-eight |
49 |
forty-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 |
Steps in the process:
- A number between 0 and 999,999,999.99 is entered in cell B2.Cell B2 is formatted as Number, with "Decimals" set to 2 places and "Thousands Separator" left unchecked.
- In Cell H9, the number in B2 is changed to a text string, and the front (left) end of the string is padded with enough zeroes to make its length 12 characters.
H9: RIGHT("000000000"&B2,12) - In cells C2 - I2 the LEFT, MID, or RIGHT function is used to extract the first, then second and third characters of each group of three before the decimal and the two after the decimal into the appropriate cell. The VALUE function changes the extracted text into a numerical value.
C2: VALUE(LEFT(H9,1))
D2: VALUE(MID(H9,2,2))
E2: VALUE(MID(H9,4,1))
F2: VALUE(MID(H9,5,2))
G2: VALUE(MID(H9,7,1))
H2: VALUE(MID(H9,8,2))
I2: VALUE(RIGHT(B2,2))
Row 3 contains a group of labels indicating the place value of the digits extracted in row 2. No formulas here—the row is just a visual aid to me while checking whether the formulas were working correctly. - In cells C4 - I4, a series of formulas looks up the the text equivalent of each one or two digit number in the cell two rows above, and adds any necessary separator or period name to the result. As written, the formulas assume English language words and North American thousands and decimal separators in both the numerical and text representations of the numbers.
C4: IF(LOOKUP(VALUE(C2),$A,$B)="","",LOOKUP(VALUE(C2),$A,$B)&" hundred")
D4: IF(AND(C2=0,LOOKUP(VALUE(D2),$A,$B)=""),""," "&LOOKUP(VALUE(D2),$A,$B)&" million")&IF(SUM(C2,D2)<1,"",",")
E4: IF(LOOKUP(VALUE(E2),$A,$B)="",""," "&LOOKUP(VALUE(E2),$A,$B)&" hundred")
F4: IF(AND(E2=0,LOOKUP(VALUE(F2),$A,$B)=""),""," "&LOOKUP(VALUE(F2),$A,$B)&" thousand")&IF(SUM(E2,F2)<1,"",",")
G4: IF(LOOKUP(VALUE(G2),A,B)="",""," "&LOOKUP(VALUE(G2),A,B)&" hundred")
H4: IF(LOOKUP(VALUE(H2),A,B)="",""," "&LOOKUP(VALUE(H2),A,B))&IF(INT(B2)>1," Euros ",IF(INT(B2)=1," Euro ","zero Euros "))&"and "
I4: IF(I2<1," zero cents",IF(I2=1," one cent",LOOKUP(I2,A,B)&" cents")) - In row 5, the blocks of text in row four are concatenated into a single string, and any extra spaces are removed by TRIM. The only formula on this row is in C5 (also true of row 6 where the only formula in C6). With text set to not wrap in these cells, any sting too long to fit in the cell flows into the space held by the adjacent cells to the right. The text exists, though only as the result of the formula in column C, and any formula collecting it does so from this row of column C.
C5: TRIM(CONCATENATE(C4,D4,E4,F4,G4,H4,I4)) - In C6, a final bit of housework—the first character of the string is split off by LEFT, changes to upper case by UPPER, and rejoined to the rest of the string (extracted by RIGHT) by the concatenation operator (&).
C6: UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)−1)
For ALL formulas: the letters, numerals and colon at the beginning are NOT part of the formula. They tell you where to place the formula, which starts with the first non-space character following the colon.
Regards,
Barry
PS: You can find the earlier version of this table posted by mswint in the forums at iworkcommunity.com
Search for check writer for it and other examples.
B