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