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
Visit us for daily dose of Excel Tips & Tricks at www.exceltip.com/
In case of any simple or complicated "Excel" query, you can ask to our experts for instant solution at www.excelforum....

[@davidnarcis533]

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

[@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.

[@ExcelTip]

+George Schott Hi George, Yes it will work even if your names are over 255 characters in length. The only small changes you have to make in the last name is that instead of picking up 255 characters, you can use use the total length of original text which would help you to meet your desired output. Let us know if that works for you.

[@alg12700l]

Excellent! Thank you

[@white1soul]

Thank you.. You are awesome

[@krishanamoro6805]

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

[@lwong1836]

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.

[@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?

[@SIMRANKAUR-eg7nc]

Find the position of first space I.e "_" with search function say 3 and subtract this from len(text) say 20. Now u need to extract 20-3 From the right with right function.
ruclips.net/video/FbcXl4BP0AI/видео.html

[@MrDarkwhale]

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

[@ExcelTip]

+Awafi Good Point.
The customized function used in this tutorial, would not work in case of First & Mid name are same. But if you have first and last name same and middle initial different or not available, then this technique would work perfectly correctly.
You can use the following simple and easy customize function to extract middle name in case of first and middle or first, mid and last name all are same.
Assuming you have text value in A1 and you are looking to extract middle name in C1, then the formula would be..
"=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1-SEARCH(" ",A2,1)))"
Do let us know if that works for you!
Happy Learning!
Team Excel Forum

[@chandank8848]

Awesome....thank you so much....

[@sultanhossen4359]

Thank you so much....

[@mikeuk1954]

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

[@ExcelTip]

+Mike Biggs Nice catch!! :) Well we have taken some random texts as names for this tutorials. So consider that as name only.

[@ExcelTip]

+mark99k Yup we didn't realize that it was asked by different user :P (Our bad ;) )..
You are correct. That can be done by testing content but extracting titles too could be done by using simple easy techniques. It would be great if sample data can be shared with us here.

[@EMISOPL2010]

What if names and surnames are mixed up?

[@mikeanderson843]

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.

[@newtonihoeghian8155]

@Mike; Very good contribution, the only correction is in the middle name replace "D1" with "C1"

[@hunter5396]

Why do we need 255 spaces again?

[@rvared]

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

[@rajkumar-xe4up]

How to Last number to first number non formula

[@balance1772]

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

[@MaheshYadav-xd9sr]

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

[@KR-nh4qw]

Are you Apu from the Simpsons?