Formula help.

[serge]

Hi,

I'm using this formula :
=MID(L1,1,1)+0
=MID(L1,2,1)+0
=MID(L1,3,1)+0

But when the number start with a "0" the third formula return " #VALUE! ".

What need to be corrected for the first formula to return a "0" if it's start with a "0" ?
Or maybe using another formula.
Thank you.

 

[NdNoviceHlp]

Hi serge. Maybe something like this...

=IF(MID(L1,1,1)=0,0,MID(L1,1,1))
=IF(MID(L1,1,1)=0,MID(L1,1,1),MID(L1,2,1))
=IF(MID(L1,1,1)=0,MID(L1,2,1),MID(L1,3,1))

HTH. Dave

 

[Phuoc]

Try this:

=MID(TEXT(L1,"000"),1,1)+0

=MID(TEXT(L1,"000"),2,1)+0

=MID(TEXT(L1,"000"),3,1)+0

 

[bebo021999]

Singgle formula in M1:
=MID(TEXT($L1,"000"),COLUMN(A:A),1)
Drag down and accross

 

[serge]

Thank you for the formula Phuoc, but it's not giving me the right answer !.

 

[serge]

Hi bebo021999, your formula is perfect, Thank you.