# Split Names (Some with Middle Initial)

1. ## Split Names (Some with Middle Initial)

Hi All,

I have a long list of names in one column, some just first and last, some with middle initial and some with middle name.

What I need to do is just end up with a list in 2 cols, Firstname and Lastname

Any help is appreciated.

Thanks

2. ## Re: Split Names (Some with Middle Initial)

so you have like John Q Public in cell A2 and want John in B2 and Public in C2.
try these
=LEFT(A2,FIND(" ",A2)-1) in B2 and
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) in C2

3. ## Re: Split Names (Some with Middle Initial)

Enter formula in cell B2 and drag formula across to cell D2 and down
Formula:
`Please Login or Register  to view this content.`

 v A B C D 1 Names First Name Middle Name Last Name 2 Justin Wolf Justin Wolf 3 Allan J Green Allan J Green 4 Jerry Grant Jerry Grant 5 Steven B Paul Steven B Paul 6 Henry Ward Beecher Henry Ward Beecher

4. ## Re: Split Names (Some with Middle Initial)

UDF (user defined function) will do?

5. ## Re: Split Names (Some with Middle Initial)

you marked it as solved, so what did you use?

6. ## Re: Split Names (Some with Middle Initial)

In C2 then copied down

=LEFT(B2,FIND(" ",B2)-1)

In D2 then copied down

=RIGHT(B2,LEN(B2)-AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(B2)))/(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=" "),1))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1