Excel boasts hundreds of tools for analyzing and organizing massive datasets. The Text to Columns tool and the TEXTSPLIT function allow users to split a full name into first and last names easily.
When downloaded from online resources or exported from a company’s data management tools, a worksheet often contains a column with full names.
Separating the first and last names manually is both time-consuming and frustrating if there are hundreds of rows.
So, it is better to automate such tasks.
Welcome to this guide, where we have discussed how to separate first and last name in Excel. A couple of built-in features and functions are explained in detail, so make sure to read the article to the end.
You can click on the following links to jump to the specific sections of the article:
- Using the Text to Columns feature
- Using the TEXTSPLIT function
- Combining the LEFT, RIGHT, FIND, and LEN functions
Download the Example Excel Sheet
You can download the following Excel Sheet and follow me as I go through this article. It contains spreadsheets with example datasets used to demonstrate various functions for separating first and last names in Excel.
If you have your own spreadsheet ready with the required dataset, then skip downloading the above file.
How to Separate First and Last Names in Excel
There are three methods to separate first and last names in Excel. You don’t need to be an expert to learn any of these methods.
We will be using various functions, so ensure you properly learn the syntax for each function discussed in the following section.
Here is the example table, which contains the full name in column A.
Our task is to extract the first in column B and the last name in column C.
METHOD #1 – Using the Text to Columns feature in Excel
This method is pretty straightforward and quick compared to the other methods discussed below. You can split the column, including names that follow the same pattern, such as only first and last names or first, middle, and last names.
As you can see in the following image, in our case, the full names include first name at the beginning and last name at the end.
Since a space generally separates the first and last name, we will use it as a delimiter.
Here are the steps:
- Open the desired Excel Sheet
- Select all the cells containing the full name
- Go to the “Data” tab from the main menu
- In the “Data Tools” group, click on the “Text to Columns” option
- A new “Convert Text to Column Wizard” will appear on the screen
- In the first step of Convert Text to Column Wizard, select the “Delimited” option and click on the “Next” button as shown below
- In the Second step, select “Space” as the delimiter and uncheck the remaining options. Once done, click on the “Next” button
(Since spaces separate all the first and last names, we have selected the “Spaces” as delimiters) - In the last step, you need to select the data format and destination. Since we are dealing with text, we can choose either “General” or “Text” and enter the cell reference where you wish to display the result in the “Destination” box, as shown below
- Click on the “Finish” button to see the result
Excel will instantly separate the first and last names from the full names. As you can see in the above GIF, column A previously held the full names, but after using the Text to Columns tool, the first and last names are populated in columns B and C.
All you need to do is define the delimiter and keep on pressing the Next button as discussed above.
PRECAUTION
Considering we have full names in Column A, you must make sure that Columns B and C are totally blank. They should not contain any text strings or values while using the Text to Columns feature.
METHOD #2 – Using the TEXTSPLIT function in Excel
In this method, we will be using the TEXTSPLIT function in Excel to split the full names.
But before we jump into the actual steps, let us understand the function first.
Explained: The TEXTSPLIT Function in Excel
It is used to split text strings depending on the delimiter specified by the users. The function creates a dynamic array that automatically spills the results into multiple cells.
The General Syntax of the TEXTSPLIT function is as follows,
=TEXTSPLIT(text,col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) Each argument of the above function needs to be replaced with the proper information.
- “text” – It needs to be replaced with the text string that you wish to split. You can manually enter the text string or provide a cell reference containing
- “col_delimiter” – This argument is used to decide the delimiter to split the text string available across the column
- “row_delimiter” – It is an optional argument. Similar to the col_delimiter, you can use this argument to define a delimiter that splits the text strings across rows.
- “ignore_empty” – It is another optional argument that lets users specify whether to ignore the empty cells or not. You can put “TRUE” to ignore empty cells and “FALSE” to create empty cells for consecutive delimiters without a value in between
- “match_mode” – It is used to define case-sensitivity. Replaced with the “1” for case-insensitive and “0” for case-sensitive
- “pad_with” – Here, you need to specify a value for padding when the split results in fewer items than expected
The syntax may feel a bit complex to you due to a lot of arguments. But except for the first three, all of the remaining arguments are optional.
Now, let us use this function to separate first and last names.
We will consider the example table as discussed above.
- Click on the cell “B2“
- Type “=TEXTSPLIT”
- Select the first option from the popup or press “Tab” key
- Replace the “text” argument with the cell reference “A2“, as it holds the full name we wish to split
- Press “,” to move to the next argument
- As the first and last names are separated by space, we will put ” “ (space enclosed in double quotation) for the “delimiter” argument
- As the remaining arguments are optional, we will ignore them
- Let’s directly use the “)” symbol and close the brackets
- Press the “Enter” key
After following the above steps, Excel will instantly split the full name into first and last names, as shown in the above GIF.
To apply this formula for the rest of the cells, you can either use the Fill Handle. Go to the lower-rightmost corner of the cell “B2” until you see the “+” icon, as shown in the following image,
Click on that “+” icon and drag it to the end of the table.
METHOD #3 – Using the Combination of the LEFT, RIGHT, FIND, and LEN function
This is the final method on this list, which includes the use of a range of built-in functions. It is quite a complex method compared to the other two discussed previously.
Explained: The LEFT function in Excel
The LEFT function in Excel returns the specific number of characters from the start of the text string defined by the users.
The syntax of the LEFT function is as follows,
=LEFT(text, [num_char])Where,
- “text” – Here, you can define the text string from which you wish to extract a substring.
- “num_char” – It is an optional argument used to specify the number of characters, starting from the left side of the string.
If the second argument is omitted, Excel will assign the value “1” and return all characters included in the cell.
Explained: The RIGHT function in Excel
The RIGHT function is similar to the LEFT function, except it extracts a portion of the text string from the end.
The general syntax for the RIGHT function is as follows,
=RIGHT(text, [num_char])Both arguments are similar to the LEFT function discussed above.
Explained: The FIND function in Excel
The FIND function is used to return the position of the given character within a text string. It helps you locate a specific character.
The general syntax for the FIND function is as follows,
=FIND(find_text, within_text, [start_num])The first two arguments are compulsory, and the last one is optional. Let’s discuss them in detail.
- “find_text” – This argument lets users define the character or substring they want to find
- “within_text” – This argument represents the text string holding the character which you wish to locate
- “start_num” – It is an optional argument. It specifies from which character the search shall begin. If omitted, the search starts from the first character
Explained: The LEN function in Excel
The LEN function in Excel returns the number of characters in a given cell. It is a simple function with the following general syntax,
=LEN(text)Here, the “text” argument needs to be replaced with the cell reference holding the text string.
It counts,
- Letters
- Numbers
- Spaces
- Special characters
That’s it! Now, let us combine these functions to separate first and last names from the full names.
STEP #1 – Getting the first name from the Full Name
In this step, we will extract the first name using the LEFT and FIND functions in Excel.
Here are the steps,
- Click on the cell “B2“
- Type “=LEFT”
- Select the first option from the popup or press “Tab” key
- For the “string” argument, we will provide the cell reference as “A2“
- Press “,” to move to the next argument of the LEFT function
- Type “FIND“
(As the FIND is a function in Excel, you will notice a popup as shown below) - Select the first option from the popup
- For the “find_text” argument of the FIND function, we will put space as ” “
(Make sure to use double quotation marks; otherwise, the formula will return an error at the end) - Press “,” to move to the next argument of the FIND function
- Provide the cell reference as “A2” in place of the “within_text” argument
- Close the bracket for the FIND function using “)“
- Now, you need to put “-1“
- Close the bracket for the LEFT function using the “)“
- Press “Enter“
Here’s what our final formula looks like,
=LEFT(A2,FIND(" ",A2)-1)It will instantly extract the full name, as shown in the above GIF.
STEP #2 – Getting the Last Name from the Full Name
Next, we will extract the last name from the full name using the RIGHT, FIND, and LEN functions in Excel.
Let’s begin,
- Click on the cell “C2“
- Type “=RIGHT”
- Select the first option from the popup or press “Tab” key
- Provide the cell reference “A2” in the place of the “string” argument
- Press “,” to move to the next argument of the RIGHT function
- Now, we will type “LEN“
(As the LEN is a function in Excel, you will see a popup as shown below) - Select the first option from the popup
- Provide the cell reference as “A2” for the “text” argument of the LEN function
- Close the bracket for the LEN function using the “)” key
- Next, type “–” symbol
- Type “FIND” and select the first option from the popup
(As the FIND is a function in Excel, you will see a popup as shown below) - Replace the “find_text” argument of the FIND function with the space as ” “
(Make sure to use quotation marks while putting the space in the formula) - Press “,” key to move to the next argument of the FIND function
- Replace the “within_text” argument with the cell reference “A2“
- Close the bracket for the FIND function using “)“
- Similarly, close the bracket for the RIGHT function using the “)“
- Press the “Enter” button on your keyboard
Our final formula should be as follows,
=RIGHT(A2,LEN(A2)-FIND(" ",A2))Now that we have our first and last names extracted from the first full name in cell “A2,” let’s apply the same formulas to the entire column.
Select “B2” and “C2.” In the lower-rightmost corner of the cell “C2,” you will see a “+” icon. Double-click on it to apply the formula for the rest of the cells.
To Summarize: How To Separate First and Last Name In Excel
Separating first and last names in Excel is easy using the Text to Columns tool and the TEXTSPLIT function.
You can also create a formula using various built-in functions to split the full names, as discussed in the third method.
I hope this article taught you everything about splitting full names in Excel. Feel free to comment below if you are stuck or have any particular issues.
Also, make sure to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.