display a number in one cell as text in more than one cell?

[Guest]

Hi,
I need to be able to display a number eg. $18735.67 across mulitple cells
as text. I need the numbers to read as follows: ONE EIGHT SEVEN THREE
FIVE 67 where the spaces between each number denote separate columns and
the cents are displayed as numerical. Any help would be much appreciated.

Storm

 

[robert111]

make a lookup table as below

1 one
2 two
3 three

etc etc

assume number is 1234.56 and is in cell A1

B1= text(A1,"0.00") ie it still LOOKS like 1234.56
C1 =left(B1) ie 1
D1 = mid(A1,2,1) ie 2

and so on

You may need to check the length of the text string =len(B1)
and where the decimal is =search(B1,".")
to make it universal

You can either have all these formulas in separate columns or combin
them for tidyness

 

[Guest]

Hi Robert,

Thanks for your answer but I am not that up there with creating formula
with that many variables.... But you have given me an idea that I will
investigate further. IF...THEN. Just need to tackle length's and positions

Janelle

 

[Bryan Hessey]

in cell A1 is your amount

for a 7 digit dollar amount,
in cell B1 put

=TEXT(A1,"0000000.99")

in cells C1 to C10 put the words
Zero
One
Two
~~
Eight
Nine

in the first amount-word cell put

=OFFSET($C1,MID($B1,COLUMN()-3,1),0,1)

and formula drag this to the right.

in the .cents cell put

=TEXT(MOD(B1,1),".00")

this should give you what was requested.

--

 

[Guest]

Thanks Brian ;-)

Excellent Works Perfectly.

Janelle

 

[Bryan Hessey]

Good to see, and thanks for your response.
--

Thanks Bryan ;-)

Excellent Works Perfectly.

Janelle

 

[Guest]

Hi,
I need to be able to display a number eg. $18735.67 across mulitple cells
as text. I need the numbers to read as follows: ONE EIGHT SEVEN THREE
FIVE 67 where the spaces between each number denote separate columns and
the cents are displayed as numerical. Any help would be much appreciated.

Storm