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

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

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

Thanks Brian ;-)

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

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

"Storm" wrote:

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