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

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

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

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