Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) -- Rikuk ------------------------------------------------------------------------ Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559 View this thread: http://www.excelforum.com/showthread...hreadid=548695 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) -- Rikuk ------------------------------------------------------------------------ Rikuk's Profile: http://www.excelforum.com/member.php...o&userid=26559 View this thread: http://www.excelforum.com/showthread...hreadid=548695 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) Mathematically, one could use: =MOD(INT(A1/10),10) =MOD(A1,10) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
separating numbers and letters from alphanumeric cell contents | Excel Worksheet Functions | |||
numbers being entered show in formula bar but not in cell? | Excel Discussion (Misc queries) | |||
Averaging Numbers when 2 numbers in one cell | Excel Worksheet Functions |