In this article we will show you how to extract First, Middle & Last Name from a given text string using multiple methods in Microsoft Excel.
Many a times you face a situation where in you are given a name list containing full names and you need to separate them on the basis of first, middle and last name and then return the result into different cells. This is a very common problem for any excel user, and you too must have come across it.
This is a very common distress for a lot of Excel users but there is no specific in-built function in Excel that can extract first name, middle initial and last name from a cell that contains a complete name.
But, there are multiple methods in Excel that can help you in doing the same. And, that’s what this article lesson is about.
Today, we will show you two different methods that can be used for splitting names into different cells.
The most common way in which Excel stores name is by taking the first name first, space then middle initial (if any) followed by space and then the last name. Sometimes, instead of spaces, comma is used for separations.
Moving on to the first example, as you can see there are a few names in “column A” that we want to extract and return in column B, C and, D as first name, middle initial and last name respectively.
Example 1
Note: Random sample names are used in this tutorial, just to explain how to extract values and return them in different columns.
Here, we will use the “Text to Column” feature which is used to separate simple cell content as first name and last name into separate columns. This feature is located under “Data” tab in “Data Tools” category. And, you can also use the keyboard shortcut “ALT + D + E” (Learn more Keyboard Shortcuts) to access the “Text to Column” wizard.
Select the range from A9 to A17 and then press “ALT + D + E”.
And, now we need to follow 3 easy steps, and as you can see on top of the wizard box, we are at the first step.
In the first step, we need to tell Excel that how our data is stored and formatted in the Excel file.
Now, we are in 2nd step of this wizard, and as you can see below, here we need to set the delimiters which our data contains.
Here you can see few “Delimiters” checkboxes; so, either you can select these check boxes or can mention the delimiter in “Other” checkbox. If your data contain some other delimiters which are not listed here. You can select one or multiple delimiters if there are more than one delimiter in your data.
Moving on to the last step of the “Text to column” wizard.
Note that if you do not specify a new destination cell reference, the extracted columns will replace the original data.
Now you can see that the names in “column A” have been extracted in multiple columns. But, if you look closely you will notice that in the 11th row, “Steve Smith” does not have a middle initial and his last name is moved to the middle name column.
That’s because, “Text to column” is only used to separate simple content into different cells basis on their delimiters. And, as there is only a single space in his name, it got split only in next 2 cells. This is the only drawback this feature has.
Example 2
Note: In the second example, we have taken “Mayor Tom C Bradley as the full name in cell H9, just to show users that when there are more than 1 initials in the middle name, the function will extract both of them.
Now, we move on to the 2nd example, where we will use multiple functions together to get what we are looking for. This is the advanced technique of extracting “First”, “Middle” and, “Last Name” into different columns. And, to do the same, we have taken similar data in column H and, we are going to have names separated in three parts as First, Middle & Last name in column I, J and, K respectively. B
This is the best solution to consider when you want to split names into “First”, “Middle” and, “Last” but do not want to repeat the previous method every time you do that. You can put the formulas in the cells and the moment reference cell will be updated, names will be extracted automatically in specific columns where you have entered the formulas.
To extract First Name
And as you can see, the function has extracted the first name from H9 and returned it in cell I9 with “Mayor”. As you know, LEFT function is used to extract leftmost character and SEARCH function returns the position of the specific character it finds first in the string. So, here SEARCH function found the space first at 6th position in the string and to exclude space, we supplied negative value which helped LEFT to pick up only 5 characters.
To extract Last Name
To extract the last name, we have nested REPT, and SUBSTITUTE function inside RIGHT function. Then at last, we nested all conditions into TRIM to remove all unwanted spaces. Let’s see how all these statements worked together to return the last name.
REPT is nested inside SUBSTITUTE which repeated single space 255 times, and then SUBSTITUTE replaced single space with 255 spaces in the supplied string. And now each initial is separated by 255 spaces and then RIGHT function is used to extract topmost 255 characters and then TRIM is used to remove all extra spaces and then return with only last initial of the name.
To extract Middle Name
Update: In case you have to separate middle name which is same as the first or last name, use the following formula in column J instead of the formula mentioned above.=TRIM(MID(H9,LEN(I9)+1,LEN(H9)-LEN(I9)-LEN(K9)))
Extracting middle initial looks quite difficult as we need to make sure that the function evaluates all conditions and extract only middle name from it. For that, we have nested LEN, SUBSTITUTE, TRIM functions inside IF
In the first argument of IF function, logical test is checking that the length of the string without space when subtracting from the total length of the string is greater than 1 or not. When the result is TRUE, function execute the TRUE condition where SUBSTITUTE is nested inside SUBSTITUTE. Nested SUBSTITUTE is replacing the value of column I with empty text in column H and then the main SUBSTITUTE is replacing column K’s value with empty text and then to remove not required spaces, TRIM is used. And, in case IF condition does not meet the logical test, then the function would return empty text.
This is how you can use customize functions & simple methods to extract “First Name”, “Middle Initial” & “Last Name” from cell that contains a full name.
Check out this interesting video tutorial where we talk in detail about all the functions and techniques used to extract names into different cells.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
How would you propose to deal with last names comprised of more than one word? E.g., Von Berg
Additionally, Jr, Sr, III, etc?
Hi Justin,
The following formula will help you in extracting names even when the surname has more than 1 word in it.
Here, we are assuming that the full name (stored in cell A2) has a first name, a middle name and last name that has 2 or more words in it.
=IFERROR(RIGHT(A2,LEN(A2)-SEARCH(" ",A2)-LEN(TRIM(MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))))-1),"")For any other Excel query that you might have, please visit our website http://www.excelforum.com to get customized solutions from our experts team of Excel.
Note, that you can make shorter version of the above formula by just referring to cells that store first & middle initials.
Happy Learning,
Team Excel Tip
Hi,
First, I noticed a typo in the text describing the first solution proposed, where "Steve Smith" in cell A9 is referred to as "David Smith".
Second, while you state that the first solution does not work correctly for some of the sample data, the second suggestion does provide a working solution for all the sample date either!
In the sample spreadsheet, cell H9 contains the full name "Mayor Tom C Bradley"; obviously "Mayor" is his title, "Tom" is his first name, "C" is the initial letter of his middle name, and "Bradley" is his surname. The provided solution is overly simple, so that it erroneously extracts "Mayor" as his first name and "Tom C" as his middle name.
Similarly, cell H15 containing "Uncle Tom" and cell H17 containing "Dr.K. S. William" would both produce spurious results using the given formula.
Hi George,
Thank you for notifying us about the errors you found. we have updated the article and made the required changes.
Also the name taken in cell H9 is used just to show users that when there are more than 1 initials in the middle name, the function extracts both of them.
Happy Learning,
Team Excel Tip
Thanks.
Thank you for truly valued knowledge you are providing, this topic was irritating thing since I can gather many columns together by "concatenate" but never knew how to split it back in excel, i use to go around by copying to word, convert table to text then convert text to multiple columns then take it back to excel. It was doable but messy headache shit.
Thank you again.
M. Sabbagh.
I used to use Concatenate all the time until I found out about using &. When I attach columns together for names I use the formula like this =A1&" "&B1
John
Surely the easiest thing to do in this instance would be to do the initial text to column but where the surname does not go to the correct column due to not having a middle name you simply follow these steps:
Sort the Surname column by A-Z.
Scroll down to the last entry in the surname column (ctrl + shift + down arrow on the top cell with an entry)
In the next row down from the last entry the surname will be blank but there will be multiple entries in the middle name column.
Simply highlight all 'middle name" entries and drag and drop into the "surname" column.
That would work unless you are working on a massive spreadsheet with thousands of rows. but there are simple formulas that would work if you filtered your idea and then simply did something like Ctrl+R and then delete the middle name column.
🙂
Brandy
Useful but very elementary and not very workable in the real world because as George points out, erroneous results occur in all but the simplest name structures. Consider the following example name structures:
John Doe
John Q. Doe
John Q. R. Doe
Mr. John Doe
Mr. John Q. Doe
John Doe, Jr.
John Q. Doe, Jr.
Mr. John Doe, Jr.
John Doe, Jr., M.D.
Mr. John Q. R. Doe, Jr., CPA
We could continue with numerous permutations and combinations that would illustrate the various ways in which name lists are typically presented. At least seven output columns are needed, as follows:
Prefix (title)
First Name
Middle Name 1
Middle Name 2
Last Name
Suffix 1
Suffix 2
Can you provide formulas for each such column? The biggest challenge I foresee is determining whether or not the first item in the name is a first name ("Tom") as opposed to a prefix ("Mayor").
Try Flashfill option in excel 2016, No more formula for this type request.
https://www.youtube.com/watch?v=YPG8PAQQ894
I'm glad I read the entire article because I really enjoyed the use made of SUBSTITUTE. Nevertheless, I was disappointed that the method did not address separating honorifics.
Additionally, I suppose it's too much to hope for a method that solves for cognomens (family names) such as la Moya; de Paul, etc.
As M Sabbagh bemoans, managing these data are a messy headache, even when the sample is counted in dozens.
Hi Sean,
Thanks for your valuable time for reading our article.
Regarding addressing honorifics, we can do that depending on the requirement we get. We prepared this article keeping in mind the the most common corporate data separation requirements that people face at work place, where usually data sets does not contain honorifics along with the names. We can customize the function formulas used here to meet specific requirements based on personal needs. to know more, visit our website.
Happy Learning,
Team Excel Tip
can you update to provide an example of how to exclude Mayor from being selected as the first name in example 2?
Hi Brad,
In case you want to extract "Tom" as the first name, here is the formula:
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)-1)Note that, A2 stores string "Mayor Tom C Bradley"
Best,
Team Excel Tip
The function to extract last name is truly genius. I wish I knew it last year ... BTW, the function for the middle name can produce errors, if middle name is the same as or inclusive of first name or last name. For example, input "Dianne Johnson John" in column H and you will find "son" in column J. Because of this issue, a more straightforward function is better: "=TRIM(MID(H9,LEN(I9)+1,LEN(H9)-LEN(I9)-LEN(K9)))"
Hi Rakkoo,
Yes, you are right. That will be the case in case we have a common middle initial as first or last name. In that case, your method would work great!.
We have incorporated your solution in our article. Thanks for your feedback. 🙂
Happy Learning,
Team Excel Tip
I've always extracted data using Left, MID and Right functions but using the Text to Column wizard is so much easier. Thank you.
Hi Julir,
Yeah, Text to column is one of the great features for basic requirements, however when you get customize requirements, we have to find out the customize solution as well. 🙂
Keep learning with us!
Best,
Team Excel Tip
In VBA I use the below:
Imagin Column K is the target of the String wanted too be separated into 10 separate parts Max, the rule for separation is the " "(1 space)
Dim varMyItem As Variant
Dim lngMyOffset As Long, _
lngStartRow As Long, _
lngEndRow As Long
Dim strMyCol As String
Dim rngCell As Range
lngStartRow = 2 'Starting row number for the data. Change to suit.
strMyCol = "K" 'Column containing the data. Change to suit.
Application.ScreenUpdating = False
For Each rngCell In Range(strMyCol & lngStartRow & ":" & strMyCol & Cells(Rows.Count, strMyCol).End(xlUp).Row)
lngMyOffset = 0
For Each varMyItem In Split(rngCell.Value, " ")
'1st column copy
If lngMyOffset = 0 Then
rngCell.Offset(0, 1).Value = varMyItem
'2nd column copy
ElseIf lngMyOffset = 2 Then
rngCell.Offset(0, 2).Value = varMyItem
'3rd column copy
ElseIf lngMyOffset = 4 Then
rngCell.Offset(0, 3).Value = varMyItem
'4th column copy
ElseIf lngMyOffset = 6 Then
rngCell.Offset(0, 4).Value = varMyItem
'5th column copy
ElseIf lngMyOffset = 8 Then
rngCell.Offset(0, 5).Value = varMyItem
'6th column copy
ElseIf lngMyOffset = 10 Then
rngCell.Offset(0, 6).Value = varMyItem
'7th column copy
ElseIf lngMyOffset = 12 Then
rngCell.Offset(0, 7).Value = varMyItem
'8th column copy
ElseIf lngMyOffset = 14 Then
rngCell.Offset(0, 8).Value = varMyItem
'9th column copy
ElseIf lngMyOffset = 16 Then
rngCell.Offset(0, 9).Value = varMyItem
'10th column copy
ElseIf lngMyOffset = 18 Then
rngCell.Offset(0, 10).Value = varMyItem
'max 10 groups
End If
lngMyOffset = lngMyOffset + 2
Next varMyItem
Please help with detail on how to print payslip from excel , by which title header will be print with each row and the deduction from total and net can be separated on a sheet.
thanks
As mentioned in the comments above, neither Excel's 'Text To Columns' utility nor the SUBSTITUTE formula approach, while useful and certainly good to know about, handle all of the cases frequently encountered in real-world names-list data.
Here's a macro solution that I think handles all of the cases mentioned above.
It works with the following list of names, splitting each name appropriately into 5 cells to the right / into Title, First Name, Middle Name(s), Last Name, and Suffix segments.
Mayor Tom C. Bradley, Jr.
David R P Jameson
Mr. Steve Smith, Sr.
Mr Kevin P O'Neill
Thomas Michael Mark la Hoya
Ms Evelyn A de Paul
Robert James Harris
Charlie Ray Preston
Bob Martin Taylor III
Governor David R David
Mrs Mary-Kate E Van Oliver
Attorney General Eric Holder
John K Smith
Vice President Joe P Biden, Esq.
Three Excel Tables are required: TitleList, FamilyList, and SuffixList. For testing, I set up the following:
TitleList
Col_1
Mayor
Governor
Mr
Mrs
Ms
Vice President
Attorney General
FamilyList
Col_1
de
la
Van
SuffixList
Col_1
Jr
Sr
II
III
Esq
As with any Excel Table, adding a new value to the row below the current table automatically redefines/extends the Table to include the new value. This makes it easy to add criteria as data are encountered with unanticipated Titles, Family prefixes, and/or Suffixes.
The macro works on a column of names anywhere in a worksheet. The cursor just has to be on the first name in the list. A confirmation MsgBox prompt, ScreenUpdating commands, etc., could be added before/after the basic parsing loop below.
Here's the code:
Sub xParse_Names()
'--------------------------------------------------------------------
Dim I As Integer
Dim J As Integer
Dim xStr As String
Dim xSuffix As String
Dim xArray As Variant
' PARSE NAMES LIST
'--------------------------------------------------------------------
I = 0
Do Until ActiveCell.Offset(I, 0) = vbNullString
' CLEAR CURRENT OFFSET CELLS / NULL XSUFFIX
'------------------------------------------------------------------
Range(ActiveCell.Offset(I, 1), ActiveCell.Offset(I, 5)).Clear
xSuffix = vbNullString
' REMOVE ANY COMMAS OR PERIODS FROM CURRENT NAME
'------------------------------------------------------------------
xStr = ActiveCell.Offset(I, 0)
xStr = Replace(xStr, ",", "")
xStr = Replace(xStr, ".", "")
' SPLIT CURRENT NAME / ENTER -> OFFSET CELLS
'--------------------------------------------------------------------
xArray = Split(xStr, " ")
For J = 0 To UBound(xArray)
ActiveCell.Offset(I, J + 1) = xArray(J)
Next J
' CHECK FOR 2-PART TITLE
'--------------------------------------------------------------------
xStr = ActiveCell.Offset(I, 1) & " " & ActiveCell.Offset(I, 2)
If Not IsError(Application.Match(xStr, Range("TitleList"), 0)) Then
ActiveCell.Offset(I, 1) = xStr
ActiveCell.Offset(I, 2).Delete Shift:=xlToLeft
End If
' CHECK FOR NO TITLE
'--------------------------------------------------------------------
If IsError(Application.Match(ActiveCell.Offset(I, 1), Range("TitleList"), 0)) Then _
ActiveCell.Offset(I, 1).Insert Shift:=xlToRight
' CHECK FOR SUFFIX
'------------------------------------------------------------------
Set xRng = ActiveCell.Offset(I, 2).End(xlToRight)
If Not IsError(Application.Match(xRng, Range("SuffixList"), 0)) Then
xSuffix = xRng
xRng = vbNullString
End If
' CHECK FOR NO MIDDLE NAME
'--------------------------------------------------------------------
xColN = ActiveCell.Offset(I, 2).End(xlToRight).Column - ActiveCell.Column
If xColN = 3 Then _
ActiveCell.Offset(I, 3).Insert Shift:=xlToRight
' CHECK FOR MULTI-PART MIDDLE NAME
'--------------------------------------------------------------------
If xColN > 4 Then
For J = 4 To xColN - 1
ActiveCell.Offset(I, 3) = ActiveCell.Offset(I, 3) & " " & _
ActiveCell.Offset(I, J)
Next J
Range(ActiveCell.Offset(I, 4), ActiveCell.Offset(I, xColN - 1)) _
.Delete Shift:=xlToLeft
End If
' CHECK FOR FAMILY NAME
'------------------------------------------------------------------
If ActiveCell.Offset(I, 3) > vbNullString Then
xArray = Split(ActiveCell.Offset(I, 3), " ")
xStr = xArray(UBound(xArray))
If Not IsError(Application.Match(xStr, Range("FamilyList"), 0)) Then
ActiveCell.Offset(I, 3) = Left(ActiveCell.Offset(I, 3), _
Len(ActiveCell.Offset(I, 3)) - Len(xStr) - 1)
ActiveCell.Offset(I, 4) = xStr & " " & ActiveCell.Offset(I, 4)
End If
End If
' ENTER ANY SUFFIX
'------------------------------------------------------------------
If xSuffix > vbNullString Then _
ActiveCell.Offset(I, 5) = xSuffix
I = I + 1
Loop
'--------------------------------------------------------------------
End Sub
Follow-ups to yesterday's post ...
1: Two more declarations are needed for the code I posted to work:
Dim xColN as Integer
Dim xRng as Range
2: For some reason, code posted in a Reply gets reformatted without indentation. This makes it harder to read but it should still work.
3: Finally, when I tried copying & pasting the above posted code into a spreadsheet's VB Editor, it ended up with single-quotes, double-quotes and minus signs all replaced by some type of non-VBE character. I had to search for & replace those characters before the code would run.
> If anyone knows how to properly post code in a Reply and could let me know, that would be great.
Thanks.
Thanks for posting the code. I am trying to get it to work. I cleaned up the comment lines and quotes. I'm getting a compile error: Syntax error on this line, xColN = ActiveCell.Offset(I, 2).End(xlToRight).Column – ActiveCell.Column
Any suggestions?
"Kyaw Kyaw Naing" middle name how to correct."=IF(LEN(B10)-LEN(SUBSTITUTE(B10," ",""))>1,TRIM(SUBSTITUTE(SUBSTITUTE(B10,B11,"",1),H11,"")),"")"
how to do only one name. 🙂
What if the format of the name is: Last name, First name MI.?
Can someone help me out. I need the formula :((((