Four digit numbers need to be split up
 

=mid is not working because I have a decimal point between the numbers.


here's a better example:

Cell AO11 = 13.00
I want:
AJ11 = 1
AK11 = 3
AM11 = 0
AN11 = 0

My problem is to 0 is coming up as a blank cell instead of showing th
zero. Is there any way to show the zero

--
hyyft
-----------------------------------------------------------------------
hyyfte's Profile: http://www.excelforum.com/member.php...fo&userid=1318
View this thread: http://www.excelforum.com/showthread.php?threadid=26547

Four digit numbers need to be split up
 

here's a better example:
"better"? think it's a whole new question <g

Cell AO11 = 13.00
I want:
AJ11 = 1
AK11 = 3
AM11 = 0
AN11 = 0

Put in

AJ11: =MID($AO$11*100,1,1)+0
AK11: =MID($AO$11*100,2,1)+0
AM11: =MID($AO$11*100,3,1)+0
AN11: =MID($AO$11*100,4,1)+0

---
If you want to "fill down" just below the target cell,
as per your original post,

Put in AO12:

=IF(LEN($AO$11*100)=ROW(A1),MID($AO$11*100,ROW(A1 ),1)+0,"")

Copy AO12 down until blanks "" appear,
signalling exhaustion of digits (to max 2 d.p.) in the target cell: AO11

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
---
"hyyfte" wrote in message
...

=mid is not working because I have a decimal point between the numbers.


here's a better example:

Cell AO11 = 13.00
I want:
AJ11 = 1
AK11 = 3
AM11 = 0
AN11 = 0

My problem is to 0 is coming up as a blank cell instead of showing the
zero. Is there any way to show the zero?


--
hyyfte
------------------------------------------------------------------------
hyyfte's Profile:
http://www.excelforum.com/member.php...o&userid=13187
View this thread: http://www.excelforum.com/showthread...hreadid=265470

Four digit numbers need to be split up
 

hyyfte,

First, convert your the number in your cell (AO11 = 13.00) to text by using
the following formula: =TEXT(AO11,"##.00")

Then, use the MID function to extract every digit.

Good Luck!

JoCa

"hyyfte" wrote:


=mid is not working because I have a decimal point between the numbers.


here's a better example:

Cell AO11 = 13.00
I want:
AJ11 = 1
AK11 = 3
AM11 = 0
AN11 = 0

My problem is to 0 is coming up as a blank cell instead of showing the
zero. Is there any way to show the zero?


--
hyyfte
------------------------------------------------------------------------
hyyfte's Profile: http://www.excelforum.com/member.php...o&userid=13187
View this thread: http://www.excelforum.com/showthread...hreadid=265470

Four digit numbers need to be split up
 

On Fri, 1 Oct 2004 10:34:14 -0500, hyyfte
wrote:


=mid is not working because I have a decimal point between the numbers.


here's a better example:

Cell AO11 = 13.00
I want:
AJ11 = 1
AK11 = 3
AM11 = 0
AN11 = 0

My problem is to 0 is coming up as a blank cell instead of showing the
zero. Is there any way to show the zero?

These should work for both of your examples, and others I could think of with
similar formats:

=LEFT(SUBSTITUTE(TEXT(AO11,"0.0000"),".",""),1)
=MID(SUBSTITUTE(TEXT(AO11,"0.0000"),".",""),2,1)
=MID(SUBSTITUTE(TEXT(AO11,"0.0000"),".",""),3,1)
=MID(SUBSTITUTE(TEXT(AO11,"0.0000"),".",""),4,1)



--ron