Formula Question

N

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
 
R

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.
 
A

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top