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)

[Elkar]

For the last digit:

=RIGHT(A1,1)

For the second to last digit:

=MID(A1,LEN(A1)-1,1)

HTH,
Elkar


"Rikuk" wrote:

>
> 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)
>
>
>
>
> --
> Rikuk
> ------------------------------------------------------------------------
> Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559
> View this thread: http://www.excelforum.com/showthread...hreadid=548695
>
>

[Ron Rosenfeld]

On Mon, 5 Jun 2006 14:07:56 -0500, Rikuk
<[email protected]> wrote:

>
>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" wrote:

>
> 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
>
>
> --
> Rikuk
> ------------------------------------------------------------------------
> Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559
> View this thread: http://www.excelforum.com/showthread...hreadid=548695
>
>

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

Rik

[Ron Rosenfeld]

On Tue, 6 Jun 2006 13:02:11 -0500, Rikuk
<[email protected]> wrote:

>
>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)),"")