# Thread: Separate out first and last name Thanks: 0 Likes: 0

1. ## Separate out first and last name

Hey all!

I have a very long list of employees in which I need to extract their names into a consistent format.

I'm using the below formulas with a 99% success rate, however employees with a suffix of "JR" or "II" (etc) are throwing a monkey wrench in the calculation.

First:
=MID(P14082,FIND(" ",P14082,1)+1,256)

Last:
=LEFT(P14082,(FIND(" ",P14082,1)-1))

Here is the format of my data:

Firstname Suffix LastName MiddleInitial

I ONLY need the first and last name. Is there a formula out there that can overcome the 2% of my names that have a suffix?

Thanks for the help team!

Best,
Chris

2. ## Re: Separate out first and last name

 Column1 Column1 Text After Delimiter Firstname Suffix LastName MiddleInitial Firstname LastName

if this is representative source data use PowerQuery (Get&Transform)

Code:
```// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TAD = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Column1], " "), type text),
TBD = Table.TransformColumns(TAD, {{"Column1", each Text.BeforeDelimiter(_, " "), type text}}),
TBTD = Table.TransformColumns(TBD, {{"Text After Delimiter", each Text.BetweenDelimiters(_, " ", " "), type text}})
in
TBTD```

3. ## Re: Separate out first and last name

I've never worked with power queries before - I've tried doing some research on them, but what I've found seems to be for older versions of excel. (I have the newest version of Excel; Office 365 ProPlus 64bit) I tried opening up power pivot (which is the same as power query, I assume) and I'm not able to replicate the steps below.

COuld you provide me with additional guidance, or point me to a newbie friendly resource on this? Or is there another way to get the data I'm looking for?

Thank you!!

4. ## Re: Separate out first and last name

so you've PowerQuery built-in (Get&Transform)
and NO, PowerPivot is NOT the same as PowerQuery

5. ## Re: Separate out first and last name

select whole column with your source data
find in Data tab something like From Table and use it
then in new window find Advanced Editor and replace code there with code from the post
remeber that the name of the table must be the same in the Name Manager and in the code
(in post above this is : Table1)

or post small part of source data

6. ## Re: Separate out first and last name

Nordicrx8:

A few things to try:

Chip Person: http://www.cpearson.com/Excel/FirstLast.htm

I wrote up some stuff:

https://www.mrexcel.com/forum/excel-...ighlight=kevin

Look at post #5

7. ## Re: Separate out first and last name

Since you have the newest version of Excel, have you tried Flash Fill?

8. ## Re: Separate out first and last name

I believe you got your formulas mixed up. Anyways, try these and let me know if they work:

First name: =LEFT(A1,FIND(" ",A1)-1)
Last name: =LEFT(MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256),FIND(" ",MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256))-1)

9. ## Re: Separate out first and last name

I did the following with some helper columns:

ABCDE
1John Kennedy FitzgeraldKennedy FitzgeraldKennedy FitzgeraldJohnKennedy
2Gerald Jr. Ford RudolphJr. Ford RudolphFord RudolphGeraldFord
3James Jr. Carter EarlJr. Carter EarlCarter EarlJamesCarter
4George Bush HerbertBush HerbertBush HerbertGeorgeBush
5John Jr. Cooledge CalvinJr. Cooledge CalvinCooledge CalvinJohnCooledge
6William III Smith F.III Smith F.Smith F.WilliamSmith

Sheet1

Worksheet Formulas
CellFormula
B1=RIGHT(A1,LEN(A1)-FIND(" ",A1))
C1=IF(OR(LEFT(B1,4)="Jr. ",LEFT(B1,4)="Sr .",LEFT(B1,4)="II ",LEFT(B1,4)="III ",LEFT(B1,4)="Esq."),RIGHT(B1,LEN(B1)-4),B1)
D1=LEFT(A1,FIND(" ",A1)-1)
E1=LEFT(C1,FIND(" ",C1)-1)

10. ## Re: Separate out first and last name

I didn't account for the suffix and middle name initial in my last name formula, revised version below.

=LEFT(MID(MID(A1,FIND(" ",A1)+1,256),IFERROR(FIND(" ",MID(A1,FIND(" ",A1)+1,256)),0)+1,256),IFERROR(FIND(" ",MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256)),255)-1)

## User Tag List

#### Posting Permissions

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