Extract First, Middle and Last Name from One Cell Into Separate Cells In Excel

This video shows "How to extract First, Middle and Last Name from One Cell into Separate Cells in Excel using multiple ways".

This is one of the very common situation which almost every user must have faced in his/her life. Either they get data from other team members in such a way where the names are given in one single column and then they have to split into different cells by their First, Middle (if any) and Last Name.

We all know there is no particular in-built function in Excel which allows extracting first, middle and last name from a cell.

We've explored two methods of doing the same. And, after watching this tutorial, you will become an expert of extracting names or text into different cells.

We have created this tutorial using following features:

1. Text to Column feature
2. LEFT function
3. SEARCH function
4. IF (logical) function
5. LEN function
6. SUBSTITUTE functions
7. TRIM function
8. RIGHT function
9. REPT function

[davidnarcis]

Thank you so much Bhai for the best and easy tips knowledge...

[lwong]

I was wondering if anyone could help with this. I am needing a formula to split (Smith Gonzalez, John Doe Hank) into first and last name.

[krishanamoro]

Hello what if there are two names before middle initial how am I going to combine those two into one cell without adding another cell before middle initial. Thank you

[MrDarkwhale]

Is that work correctly if the first and last or mid name are the same

[smagD]

Sir, how to extract names without the initials like Mr., Mrs., Ms, Dr. etc.
The step where I stuck is there may be the names in the data without initials or with initials without a full stop after it like 'Mr' or 'Mr.'.
How to do it?

[GeorgeSchott]

Will this work if names are over 255 characters in length? Like if my last name is REPT("a", 300). Your formulas were very interesting, though I think they could have been optimized, not that I tried another way on my own. They certainly educated and entertained me.

[EMISOPL]

What if names and surnames are mixed up?

[mikeuk]

First line shows "Mayor" This is a title not a name? So how would you sort out "Titles" From first names?

[rvared]

Note: when the last name is followed by a space, test shows that at time you may get incorrect results

[rajkumar-xeup]

How to Last number to first number non formula

[mikeanderson]

How about using the following:Name in cell A1cell B1:  First Name:  =left(A1, find(" ", A1)-1)cell C1:  Last Name:  = trim(right(substitute(A1, " ", REPT(" ", Len(A1))), len(A1)))cell D1:  MIddle Name:  = trim(substitute(trim(substitute(A1, B1, " ")), D1, " "))If there is no middle name, then it gives first and last names in propper columns.  if there are 2 middle names, it puts both in middle name column.  Basically, the "last name" formula substitutes X spaces for each space, with X equal to the length of the name.  It then takes the first X characters from the right which has the last name and spaces substituted in.  It trims up the spaces."Middle Name" formulas just strips out the first and last names from the name and trims up any leading or trailing spaces.

[MaheshYadav-xdsr]

my full name is Oggu Alias Golla Mahesh these name split into lastname middlename firstname

[balance]

sorry sir mid and last name ko you have made it very critical....make it simple