Formula Question

[Nikki]

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

 

[Rick Rothstein]

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.

 

[Ashish Mathur]

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(ISNUMBER(1*MID($A3,ROW($1:$15)-1,1))),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

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(INDIRECT("1:"&LEN(B14))),2))),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com