Split numbers in cell

[Rikuk]

Hi I have cells that contain numbers of various lenghts how do I split
the last 2 digits only in to seperate cells

I've tried =RIGHT command but this does not work as I require

I.e

156974 split into two seperate cels would be (4) & (7)
589 split into two seperate cels would be (8) & (9)

 

[Ron Rosenfeld]

Hi I have cells that contain numbers of various lenghts how do I split
the last 2 digits only in to seperate cells

I've tried =RIGHT command but this does not work as I require

I.e

156974 split into two seperate cels would be (4) & (7)
589 split into two seperate cels would be (8) & (9)

Mathematically, one could use:


=MOD(INT(A1/10),10)
=MOD(A1,10)


--ron

 

[Rikuk]

Thanks for the replys

=MID(E3,LEN(E3)-1,1) works perfectly

However if theres no data I get a #VALUE! is there a way of removing
this or replacing with 0 "ZERO"

Rik

 

[Elkar]

Try this:

=IF(ISERROR(MID(E3,LEN(E3)-1,1),"",MID(E3,LEN(E3)-1,1))

This will show a blank if an error results. If you'd rather have a 0, then
replace the "" with 0.

HTH,
Elkar

 

[Rikuk]

Thanks for the reply, I keep getting an error with this statement

=IF(ISERROR(MID(E3,LEN(E3)-1,1),"",MID(E3,LEN(E3)-1,1))

Is there something incorrect?

Ri

 

[Ron Rosenfeld]

Thanks for the replys

=MID(E3,LEN(E3)-1,1) works perfectly

However if theres no data I get a #VALUE! is there a way of removing
this or replacing with 0 "ZERO"

Rik

The:

=MOD(INT(A1/10),10)
=MOD(A1,10)

will give a zero if the cell is empty.

But that's not a great idea, as then you would have no way of differentiating a
terminal zero from a blank cell.

Better:

=IF(A1="","",MOD(INT(A1/10),10))
=IF(A1="","",MOD(A1,10))


--ron

 

[daddylonglegs]

Thanks for the reply, I keep getting an error with this statement

=IF(ISERROR(MID(E3,LEN(E3)-1,1),"",MID(E3,LEN(E3)-1,1))

Is there something incorrect?

Rik

There's a parenthesis missing, you could try

=IF(ISERROR(MID(E3,LEN(E3)-1,1)),"",MID(E3,LEN(E3)-1,1))

or another way....

=IF(LEN(E3)>1,LEFT(RIGHT(E3,2)),""