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

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History