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
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
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
=right(A1, find(" ", A1)+1
You can get the last name from a full name in cell A1 using a formula like:
You can get the first name(s) of a full name in cell A1 and a last name in C1 using a formula like:
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.
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))
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,""))
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
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
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
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
Microsoft article on Flash Fill, with video
Your problem with circular references was probably because you put last name in column B rather than C. I've attached a sample workbook so you can see the formulas working, as well as the problem with suffixes.
NameParsing.xlsx
Your problem with circular references was probably because you put last name in column B rather than C. I've attached a sample workbook so you can see the formulas working, as well as the problem with suffixes.
NameParsing.xlsx
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
Just one minor change needed (Using the formula),
When having Full_Name = 'BAZELAIS, GONZALEZ', how can I remove the comma?
Thanks,
Ben
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)
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))
Hi,
, how can these be handled?
If solution is complicated I will open new Q.
Thanks,
Ben
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial