In this article, we will learn How to Extract First, Middle and Last Name from One Cell Into Separate Cells In Excel.
Scenario:
Splitting of text names is one of the basic tasks in excel. We often need to split names, ids, addresses etc. from a cell having all values. To do so we target a separator text in the string and use it to split each segment of string. In this article, we will learn how to split text based on space characters in the string.
Retrieve names using Excel formula in Excel
We want to split texts at space character ( " " ) using the function LEFT, RIGHT, LEN and FIND. There will of course be at least two parts of the name split. First, the text before specific text (text on left of the character) and second, the text after the specific text (text on the right of the character).
Generic formula to get the first name
| =LEFT(cell_ref,FIND(" ", cell_ref)-1) |
Cell_ref : reference of the cell where value is stored
Example :
All of these might be confusing to understand. Let's understand how to use the function using an example. Here I have this simple data set. In range A2:A1, we have names and codes concatenated with a comma (",") and space characters (" "). The name is on the left of the comma and I want to split it from each text.
Apply the above generic formula here to get text on the left of the comma in string. Copy it in B2 and drag down.
| =LEFT(A2,FIND(",",A2)-1) |
You can see that each name is extracted from the string precisely.
As we know, the LEFT function extracts a given number of characters from a given string. Now let's break down the formula inside out.
FIND(",",A2) : Here the FIND function looks for the position of the search string comma (",") in cell A2 (Georgina Schalk,30). It returns 16. Now the formula is =LEFT(A2,16-1).
Since we don't want the comma (",") to be part of the extracted string, we subtract 1 from 16. (if the target characters were 2 then we would subtract 2 and so on. In short, we subtract length of the search string from the position of the search string to not include it in the result).
Finally, we will have LEFT(A2,15) and we get the name extracted (15 characters from left).
Split text on the right of string
We learned how to get text on the left of a specific character. Now, let's see how we can split text on the right of the specific characters.
Generic formula to get the Last name
| =RIGHT(string,LEN(string)-FIND(" ", string)) |
String: The string from which you want to split text. This can be a hardcoded value, a cell reference, or a formula.
Example:
All of these might be confusing to understand. Let's understand how to use the function using an example. Here We will use the same data that we used in the above example. This time we need to extract codes from the string. The codes are on the right of the comma (","). Let's use the above generic formula to split text from right.
In cell C2, write this formula and drag down to get each value.
| =RIGHT(A2,LEN(A2)-FIND(",",A2)) |
Let's break it down from inside. The LEN function returns the number of characters in the string.
LEN(A2) returns 18. The FIND function returns 16, as it does previously. Now the formula solves to RIGHT(A2,18-16), which again resolves to RIGHT(A2,2). Now as we know, the RIGHT function returns the given number of characters from the right of a given string, hence we get 30 (2 characters from right).
So we have split text at specific text in a string using the text excel function. It is dynamic. Whenever you enter a new data column, you will get extracted text in adjacent columns (if you copy formulas in adjacent cells). Using this method you will have original text intact. If you want, you can paste the output and delete the source data.
Using VBA code to Split text by specific character
Identifying any special characters can be very important for data cleaning purposes. And in some cases, it must be done. So how can we do this in Excel? How can we know if a string contains any special characters? Well we can use User Defined Function in VBA to do so.
The below formula will return TRUE if any cell contains any characters other than 1 to 0 and A to Z (in both cases). If it does not find any special characters it will return FALSE.
Generic Formula
| =Function_name(string) |
String: The string that you want to check for special characters.
For this formula to work, you will need to put the below code in a module of your workbook.
Follow the steps as explained. So open Excel. Press ALT+F11 to open the VBA code environment. Insert a module from the Insert menu. Copy the below code and paste it into the module.
| Function Special_Char(str As String) As Boolean
For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select Next End Function |
Now the function is ready to be used.
Go to the worksheet in the workbook that contains the strings that you want to check.
Write the below formula in cell C2
| =ContainsSpecialCharacters(B13) |
It returns TRUE for the first string since it contains a special character. When you copy the formula down it shows FALSE for B14 string and so on.
But strangely it shows TRUE for the last string "Exceltip.com". It is because it contains dot (.). But why so? Let's examine the code to understand.
We are iterating through all the characters of the string using the For loop and mid function of VBA. The Mid function extracts one character at a time from string and stores it into ch variable..
| For I = 1 To Len(str)
ch = Mid(str, I, 1) |
Now the main part comes. We use the select case statement to check what the ch variable contains. We tell VBA to check if ch contains any of the defined values. I have defined 0 to 9, A to Z, a to z and " " (space). If c
h contains any of these, we set it's value to False.
| Select Case ch
Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False |
You can see that we don't have a dot (.) in the list and this is why we get TRUE for the string that contains dot. You can add any character to the list to be exempt from the formula.
If ch contains any character other than the listed characters we set the function's result to True and end the loop right there.
| Case Else
ContainsSpecialCharacters = True Exit For |
This will do it
Using the above formula, we can only separate text on the first appearance of specific text in the string. If there were multiple commas at random places in text, the above formula would be useless. We will have to use more formulas and that would be tedious. How do we split text on each appearance of a specific character?
There's a built-in feature to split text into columns. It is called Text to Column feature. Using this feature you can split original text into multiple columns, on each appearance of the separator.
Let's see how you can use split text based on a specific character. First thing, have enough empty columns so the right of the source text. If you have data in columns to the right of source text, they may get replaced. It is better to copy the source data in a new sheet. In this way, you will have your source data intact and no other data will be affected.
Select the data that you want to split.
Press ALT>A>E. This will open the Convert Text to Column Wizard box. You can also go to Data> Text to Column, in the ribbon tab.
Select Delimited and click on next.
There are few common separator options available to select, like Tab, Semicolon, Comma, Space and Other. You can select one or more separators.
Here we will split text using comma separators. We can select the comma check box, but here we will use the Other option. In another option, you can write any text to split text at. We will write a comma.
Once you write a comma in the text box, excel will preview the output before you finish.
Hit the finish button. It's done. Your data is splitted at specified character.
If we had used multiple separators, like space and comma both then we would have split first and last names.
In the first method you create a dynamic separator that you can use multiple times by just changing the data. But when you have to split text into too many parts, the Excel formula method can be complex and tedious. On the other hand, the Text to Column method is good on this part but it is not dynamic.
Here are all the observational notes using the formula in Excel
Notes :
Hope this article about How to Extract First, Middle and Last Name from One Cell Into Separate Cells In Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share them 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 to us at info@exceltip.com.
Related Articles :
Excel REPLACE vs SUBSTITUTE function: The REPLACE and SUBSTITUTE functions are the most misunderstood functions. To find and replace a given text we use the SUBSTITUTE function. Where REPLACE is used to replace a number of characters in a string.
How to use the ISTEXT function in Excel : returns the TRUE logic value if the cell value is text using the ISTEXT function in Excel.
How to Highlight cells that contain specific text in Excel : Highlight cells based on the formula to find the specific text value within the cell in Excel.
Converts decimal Seconds into time format : As we know that time in excel is treated as numbers. Hours, Minutes, and Seconds are treated as decimal numbers. So when we have seconds as numbers, how do we convert into time format? This article got it covered.
Calculate Minutes Between Dates & Time in Excel : calculating the time difference is quite easy. Just need to subtract the start time from the end time. Learn more about this formula clicking the link
Replace text from end of a string starting from variable position : To replace text from the end of the string, we use the REPLACE function. The REPLACE function use the position of text in the string to replace.
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
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.
What if the format of the name is: Last name, First name MI.?
Can someone help me out. I need the formula :((((
"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. 🙂
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?
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
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
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
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
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
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
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
Try Flashfill option in excel 2016, No more formula for this type request.
https://www.youtube.com/watch?v=YPG8PAQQ894
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").
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
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
Thanks.
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
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