Convert numbers to text

[Colleen45]

Hi there I'm having a bit of a problem converting numbers to text:
In cell F39 i have a number (for example) 4322.08
and i would like to convert into into text in the following way
In cell B47 I would like it to say Four
In cell C47 I would like it to say Three
In cell E47 I would like it to say Two
In cell G47 I would like it to say Two
In cell I47 I would like it to say Two
in cell J47 I would like it to say Eight

But essentially any number that I type into cell F39, I would like it to convert the number to text
Can someone please help me and make me smile again
Thankyou
Colleen

 

[riaz]

Hi

Sorry have been away for a couple of days. I'll get back to you but doesn't look like before Monday. I hope that's ok.

 

[Andrew Poulsom]

Riaz's formulas will work if you replace ####.## with 0000.00.

 

[al_b_cnu]

Hi,

didnt see Andrew's reply until too late

Here's my solution:

1) in another sheet (sheet2 in this example) place the following:

Book1
	A	B	C	D	E	F	G	H	I	J
1	zero	1000	100		10	1			0.1	0.01
2	one
3	two
4	three
5	four
6	five
7	six
8	seven
9	eight
10	nine
Sheet2

2) place this formula into B47 & copy across

=OFFSET(Sheet2!$A$1,INT(MOD($F$39,Sheet2!B$1*10)/Sheet2!B$1),0)

Book1
	B	C	D	E	F	G	H	I	J
39					9876.54
40
41
42
43
44
45
46
47	nine	eight		seven	six			five	four
Sheet1

 

[riaz]

Andrew is absolutely right, if you put "0,000.00" in my formula to replace the "#,###.##", for 234 you will get zero two three four zero zero as the answer. For 23.12 you will get zero zero two three one two.

If you want to hide the zero's in the boxes before the decimal, you could use conditional formatting to use font colour white if the cell value is zero.

I am not sure what you mean by there is a currency. Could you please clarify?

 

[riaz]

Oops, be careful with that conditional formatting. Wasn't thinking it through. If your answer is one two zero four point zero four, for example, you don't want to hide either of the zeros. So I guess for now its a manual job if you want to hide a zero.

 

[PA HS Teacher]

I see a couple of issues,

1. What if you have an amount that has more or less precision than two decimals.
2. What if you have a number that is bigger or smaller than 0000.00

To deal with this, I rounded to 2 decimals first.
I then multiplied by 100, so that the 1/100th place is always right rightmost digit, and the 1000s place is always the 6th digit from the right etc.

3. Since the words "One", "Two" etc are not likely to change anytime soon, I'd hardcode them in an array within the formula, making it unnecessary to maintain the list on another page.

In B47:
=INDEX({"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},MID((ROUND(F39,2)*100),LEN((ROUND(F39,2)*100))-6+1,1)+1)

notice the -6 towards the end of the formula for the 6th digit from the right or the 1000s place.

In C47:
=INDEX({"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},MID((ROUND(F39,2)*100),LEN((ROUND(F39,2)*100))-5+1,1)+1)

notice the -5 towards the end of the formula, specifying the 5th digit from the right, or the 100's place.

Book2
	B	C	D	E	F	G	H	I	J
39					$9,876.50		987650	Zero
40
41
42
43
44
45
46
47	Nine	Eight		Seven	Six			Five	Zero
Sheet2

 

[riaz]

I see a couple of issues,

1. What if you have an amount that has more or less precision than two decimals.

Hi Teacher

I like your solution. Simplifies on one line, something I need to learn. Thanks for the tip.

The reason rounding is unnecessary in this case is that the amount entered is a sum of money so there will alway be two decimals. I agree about what if the amount is greater than four significant digits, but the OP in this case asked for a particular solution with four digits. Otherwise my long winded solution could have been extended to as many digits as one would like.

Isn't it sad that Microsoft didn't think of this? It is available in Word, why not in Excel?

Cheers

 

[PA HS Teacher]

Riaz,
I like your solution as well. When formatted at "0000.00" it enforces a certain length string, so it is really not much different that what I did.

Though we are dealing with money here. There are situations where for calculations, one can have fractional currency. For example, gas stations around here traditionally price to the tenth of a cent. (e.g. 00.009). I just wanted to keep open that possibility. I believe in the movie "Superman 3" someone collected a bunch of 'rounding errors' from bank accounts and grew very rich.

 

[jkindhappy]

Hi Teacher,

Very good solution. But when I used your formula it is causing the same problems as that of Riaz's First formula. I am not that prolific user of excel or this forum as you. But can you verify it once. If I am wrong I am sorry but I am getting the same result.(if the figure in Cell F39 is 234.00 . The 00 gives an as #N/A #N/A or if Cell F39 is 23.12 it puts wrong figures in the wrong cells)

 

[PA HS Teacher]

I'm not sure. It works for me.

I've uploaded the file here:

http://www.box.net/shared/6xgkqswsgg

for shorter numbers, you will get an error message because the existing digits should show up properly.

Money to Words 7-18-08.xls
	B	C	D	E	F	G	H	I	J
39					$23.12
40
41
42
43
44
45
46
47	#VALUE!	#VALUE!		Two	Three			One	Two
Sheet1