If I have data that needs to be separated into columns that looks like this:

JaneDoe

MaryannSmith

RobertWhiteIs there a way to separate into a first name and last name column?

Thanks - Alice

Try this: With your data like:

A B C D 1) JaneDoe 2) MaryannSmith 3) RobertWhiteIn Cell B1 enter the formula:

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$1 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("A$1:A"&LEN(A1))))-1

It is a very long formula, so Copy & Paste from here.

Drag down as many rows as needed.Your data should now look like:

A B C D 1) JaneDoe 5 2) MaryannSmith 8 3) RobertWhite 7Column B has the number position of the Capital letter of the last name.

Now in cell C1 enter the formula:

=LEFT(A1,B1-1)

In Cell D1 enter the formula:

=RIGHT(A1,LEN(A1)-B1+1)

Your data should now look like:

A B C D 1)JaneDoe 5 Jane Doe 2)MaryannSmith 8 Maryann Smith 3)RobertWhite 7 Robert WhiteUnfortunately I can't take credit for all of this,

the formula that does the work of spliting up the

name was found here:http://www.excelbanter.com/showthre...

I simply modified it to fit.

MIKE

A shorter version is this Array Formula placed in B1 to extract the first name from A1: =LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

Confirmed with Ctrl-Shift-Enter

Then this non-array formula in C1 for the last name:

=REPLACE(A1,1,LEN(B1),"")

Credit goes to:

http://www.ozgrid.com/forum/showthr...

Things get much,much more complicated if you have names like:

MaryJoMcGillicutty

BillO'Reilly

RobertGriffenIII

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

alicec

Be aware that the formula I posted will Fail if there are more then

Two capitals in a name, so MikeMcConaghy (3 caps) will fail,

also if the First name does NOT start with a Capital.This updated formula will solve the second problem, of the first name

not having a capital.=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$2 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("A$2:A"&LEN(A1))))

DerbyDad03,

just a heads up, the Array Formula for the first name will Fail if

the First & Last Name have the same letter, IE MickeyMouse

also if the First name does NOT start with a Capital, IE mickeyMouseMIKE

A Million Thank Yous!

Both of these solutions were incredible. I used the second one only because of the situation of 3 uppercase letters (MaryMcNeil or JohnMcManus).I really appreciate this website and you timely response. I'll spread the word...

Alice

alicec Be aware that the name MaryMcNeil will FAIL on both mine & DerbyDad03's formulas.

Mine will give you the full name in C1 and a #VALUE Error D1

DerbyDad's will give you

MaryMcNeither one of these formula is perfect so you will still need to do some cleanup.

MIKE

Mike,

It will be a tremendous help, I will be able to do the random clean-up.Thank you and DerbyDad for answering this for me. Maybe some day I can answer a question for you! (It will not be anything to do with computers, haha)

Thanks again.

Ask Your Question

Weekly Poll