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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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.

--
 
Storm said:
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
Click to expand...
 
Back
Top