Formula Question
I have concatenated two fields but now need to separate the name from the
branch number in the example below 1001John Doe I need 1001 in one column and John Doe in the other. Column A Column B 1001 John Doe |
Formula Question
Do the two columns you concatenated still exist? If so, just use a formula
that references them directly. If not, where do you have the concatenated text at? I ask because you are saying you want the results in Columns A and B... if your concatenated text is in Column A, then you will need to use a macro to do what you want. -- Rick (MVP - Excel) "Nikki" wrote in message ... I have concatenated two fields but now need to separate the name from the branch number in the example below 1001John Doe I need 1001 in one column and John Doe in the other. Column A Column B 1001 John Doe |
Formula Question
Hi,
You can try this array formula (Ctrl+Shift+Enter) =MID(A3,MATCH(TRUE,ISNUMBER(1*MID(A3,ROW($1:$18),1 )),0),MATCH(1,ISNUMBER(1*MID($A3,ROW($1:$15)-1,1))*NOT(ISNUMBER(1*MID($A3,ROW($1:$15),1))),0)-MATCH(1,ISNUMBER(1*MID($A3,ROW($1:$15),1))*NOT(ISN UMBER(1*MID($A3,ROW($1:$15)-1,1))),0)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nikki" wrote in message ... I have concatenated two fields but now need to separate the name from the branch number in the example below 1001John Doe I need 1001 in one column and John Doe in the other. Column A Column B 1001 John Doe |
Formula Question
Hi,
Try this shorter array formula (Ctrl+Shift+Enter). I have assumed that 1001John Doe is in cell B14 =1*MID(B14,1,MATCH(FALSE,ISNUMBER(1*(MID(B14,ROW(I NDIRECT("1:"&LEN(B14))),2))),0)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nikki" wrote in message ... I have concatenated two fields but now need to separate the name from the branch number in the example below 1001John Doe I need 1001 in one column and John Doe in the other. Column A Column B 1001 John Doe |
All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com