Split Names column into First/Last name columns.

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

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

Select allOpen 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,""))

Select allOpen 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.

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

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

@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

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

Thank you!

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

Select allOpen in new window

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

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