Four digit numbers need to be split up

[hyyfte]

=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

 

[Max]

here's a better example:

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

 

[Guest]

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

 

[Ron Rosenfeld]

=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