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

 

[Krishnakumar]

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.

In B47 and copied across,

=IF(COLUMNS($B$47:B47)<=LEN(ROUND($F$39,2)*100),INDEX({"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},MID((ROUND($F$39,2)*100),LEN((ROUND($F$39,2)*100))-(LEN((ROUND($F$39,2)*100))-COLUMNS($B$47:B47)),1)+1),"")

 

[PA HS Teacher]

"=IF(COLUMNS($B$47:B47)<=LEN(ROUND($F$39,2)*100),INDEX({"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},MID((ROUND($F$39,2)*100),LEN((ROUND($F$39,2)*100))-(LEN((ROUND($F$39,2)*100))-COLUMNS($B$47:B47)),1)+1),"")"

Good approach. I thought about something similar, but hesitated because it seems really important to the original poster to have specific digits in specific nonadjacent cells.

 

[riaz]

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.

As the OP is filling out a a cash form (it could be the maximum take in a day is always less than ten thousand, as she asked to fill out four boxes), then the leading zeroes become significant so for say $234 she would need to say zero two three four etc.

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.

Not so fictional actually. This happened in real life in the 70s or the 80s, a programmer diverted third decimal interest calculations on customer balances to his own account. Obviously no rounding formula in those days! I believe he got away with a fairly large sum for those days, and when it was discovered, the bank just let him go rather than face the publicity.