Posted to microsoft.public.excel.misc
|
|
display a number in one cell as text in more than one cell?
Good to see, and thanks for your response.
--
Storm Wrote:
Thanks Bryan ;-)
Excellent Works Perfectly.
Janelle
"Bryan Hessey" wrote:
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 Wrote:
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
"robert111" wrote:
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
combine
them for tidyness.
--
robert111
------------------------------------------------------------------------
robert111's Profile:
http://www.excelforum.com/member.php...o&userid=31996
View this thread:
http://www.excelforum.com/showthread...hreadid=529517
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=529517
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=529517
|