Split numbers in cell
 

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)

:confused:


--
Rikuk
------------------------------------------------------------------------
Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559
View this thread: http://www.excelforum.com/showthread...hreadid=548695

Split numbers in cell
 

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)

:confused:


--
Rikuk
------------------------------------------------------------------------
Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559
View this thread: http://www.excelforum.com/showthread...hreadid=548695

Split numbers in cell
 

On Mon, 5 Jun 2006 14:07:56 -0500, 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)

:confused:

Mathematically, one could use:


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


--ron

Split numbers in cell
 

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

Split numbers in cell
 

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

Split numbers in cell
 

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


--
Rikuk
------------------------------------------------------------------------
Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559
View this thread: http://www.excelforum.com/showthread...hreadid=548695

Split numbers in cell
 

On Tue, 6 Jun 2006 13:02:11 -0500, 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

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

Split numbers in cell
 

Rikuk Wrote:
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)),"")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=548695