Separate out first and last name

[Nordicrx8]

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

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Text After Delimiter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Firstname Suffix LastName MiddleInitial[/td][td][/td][td=bgcolor:#E2EFDA]Firstname[/td][td=bgcolor:#E2EFDA]LastName[/td][/tr]
[/table]

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

[SIZE=1]// 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[/SIZE]

 

[Nordicrx8]

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!!

 

[sandy666]

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

 

[sandy666]

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

• what is PowerQuery
• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help

 

[kweaver]

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-questions/1102558-extracting-names.html?highlight=kevin

Look at post #5

 

[kweaver]

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

 

[Hamedo89]

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)

 

[kweaver]

I did the following with some helper columns:

Book1
	A	B	C	D	E
1	John Kennedy Fitzgerald	Kennedy Fitzgerald	Kennedy Fitzgerald	John	Kennedy
2	Gerald Jr. Ford Rudolph	Jr. Ford Rudolph	Ford Rudolph	Gerald	Ford
3	James Jr. Carter Earl	Jr. Carter Earl	Carter Earl	James	Carter
4	George Bush Herbert	Bush Herbert	Bush Herbert	George	Bush
5	John Jr. Cooledge Calvin	Jr. Cooledge Calvin	Cooledge Calvin	John	Cooledge
6	William III Smith F.	III Smith F.	Smith F.	William	Smith
Sheet1

Cell Formulas
Range	Formula
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)

 

[Hamedo89]

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)