Avatar of bfuchs
bfuchs
Flag for United States of America asked on

Split Names column into First/Last name columns.

Hi Experts,

I have a table that contains Full_Name column and would like to split it into two columns, first and last name.
Tried exporting to Excel and do Text to columns, but there are cases with 3 names, which I would like to have the first two in First_Name column and the thirt in Last_Name colum.
What is the easiest way to accomplish it?

Thanks
DatabasesMicrosoft SQL ServerMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
David Todd

8/22/2022 - Mon
Tom Farrar

You could take it to Excel Power Pivot and parse the data based on the right most space.  Or you could use a formula like

=right(A1, find(" ", A1)+1
byundt

You can get the last name from a full name in cell A1 using a formula like:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

Open in new window


You can get the first name(s) of a full name in cell A1 and a last name in C1 using a formula like:
=TRIM(SUBSTITUTE(A1,C1,""))

Open in new window


The above formulas are working with names like John Paul Jones, Bill Jones and Rihanna. They will fail with names like Aloysius Throckmorton, III or Marcus Welby M.D.
David Todd

Hi,

The best way I've ever seen this done was an article in PC Techniques Oct/Nov 1991 that did something rather clever with full names in C. If you can find this, it will handle most names.

For instance, it will correctly handle some of the European names which have prefixes on the last name such as van, de, da ... etc.

Kind regards
  David
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
byundt

If you are using Excel 2013 or later, you have the Flash Fill feature available to you. You may use that feature to split your names into first and last names without any formulas at all. To do so, make sure you have two blank columns to the right of your column of full names.

1. In the first column, type the first name(s) for the first two people
2. In the second column, type the last name for the first two people
3. Select the cells in the first of those columns all the way to the last row of full names
4. Use the Home...Fill...Flash Fill menu item to populate the cells with first names
5. Now select the cells in the second of those columns all the way to the last row of full names
6. Use the Home...Fill...Flash Fill menu item to populate the cells with last names
bfuchs

ASKER
@Tom,

Yours didn't work, some names cut off in the middle, maybe missing some tweaks.

@byundt,

The first one worked for the last name, but got the attached message on the first name.

The last suggestion didn't work, probably not sure how to follows it properly, is it possible you post a recording of those steps?

@David,
Looking for the easiest way to get it done, as its needed for a one time thing, not want to get into programming here.

Thanks,
Ben
Untitled.png
ASKER CERTIFIED SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
Okay this time it worked.

Just one minor change needed (Using the formula),

When having Full_Name = 'BAZELAIS, GONZALEZ', how can I remove the comma?

Thanks,
Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
Thank you!
byundt

If your full name data are already in the format Last Name, First Name, your problem gets much simpler. I really suggest using Flash Fill in such a situation.

Formulas for Last Name and First Name when names are stored with a comma separating last name from first name (e.g. Jones, John Paul)
=LEFT(A2,FIND(",",A2 & ",")-1)
=TRIM(MID(A2,FIND(",",A2 & ",")+1,99))

Open in new window

bfuchs

ASKER
Hi,

If your full name data are already in the format Last Name, First Name, your problem gets much simpler.
No, mostly are "FirstName LastName", however those that have a comma are "LastName, FirstName".
, how can these be handled?

If solution is complicated I will open new Q.

Thanks,
Ben
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
David Todd

Hi Ben,

The solution - in C - that I mentioned at the top takes a full name field and normalises it, so william gates iii becomes Gates, William III.

If the name is in lower case it capitalises it. If there is a comma it assumes the name is already normalised and preserves it.

I think you'll find the logic helpful.

HTH
  David