How to Separate Names in Excel: Split First and Last Names (2023)

Full names are usually typed into one cell.

When using options like sorting, and filtering, it is important to separate full names into first, middle, and last names💡

Excel offers numerous ways to separate names🤔

Using a few examples, we will learn four basic techniques to split names in Excel.

Download the attached Excel file and practice with me.

Table of Contents

Split names with functions

We can use Excel formulas with excel text functions to split data.

Let’s use Excel formulas to split the first and last names in the first column in the example below.

Names in Excel to separate as first and last name

To extract the first names in Excel, we can use two different formulas.

Formula example #1 to extract first names

To simply get the first name into the first name column, use the TEXTBEFORE function.

  1. In column B, type an equal sign and choose “TEXTBEFORE.”

Write,

=TEXTBEFORE(

TEXTBEFORE function to extract the first name.
  1. Select the cell that contains the full name.

Let’s select the A2 cell as the Text.

Now your formula is;

=TEXTBEFORE(A2

Selecting the cell to extract text of the first name.
  1. Enter a space character as the delimiter.

Now, the formula is:

=TEXTBEFORE(A2,” “

Entering a space character as the delimiter
  1. Close the parenthesis and press enter.

The first name is extracted😍

TEXTBEFORE function

Only Microsoft 365 users can presently use the above formula😕

Formula 2 to extract first names

So, let’s learn the other formula that any Excel user can apply🤗

  1. In column B, enter an equal sign and choose the “LEFT” function.
Selecting the LEFT function
  1. Select the full name.

I select cell A2.

Now your formula is;

=LEFT(A2

Selecting the text - LEFT function.
  1. Use the “Find” function to get the number of characters in the first name.

The “Find” function helps in locating a specific character’s place.

To calculate the number of characters in first names, locate the space character and subtract 1.

The formula should now look like this.

=LEFT(A2,FIND(” “,A2)-1

Using FIND function to get the number of characters of the first name.

Pro Tip!

In the above formula, you can replace the FIND function with the SEARCH function.

Both are very similar functions.

However, the FIND function is case-sensitive, whereas the other is not.

As we look for a space character position when extracting names,  case sensitivity is irrelevant.

The formula for the SEARCH function is:

=LEFT(A2,SEARCH(” “,A2)-1

Using SEARCH function to get the number of characters of the first name.
  1. Close the parenthesis and press “Enter”.

The first name is extracted😍

First name is extracted from the full name column using the LEFT function.

To get the last name out of the Excel full name column, we can use two different formulas.

Formula example #1 to extract last names

The simplest Excel formula to get the last name is the TEXTAFTER function.

  1. Enter an equal sign in the last name column and select the “TEXTAFTER”.

Write,

=TEXTAFTER(

TEXTAFTER function to extract the last name to the last name column.
  1. Select the cell that contains the full name.

So, select A2.

Now your formula is;

=TEXTAFTER(A2

Selecting the cell in the full name column
  1. Enter a space character as the delimiter.

The formula is now;

=TEXTAFTER(A2,” “

Entering a space character within quotation marks as there is a space character before the last name

If the full name has a middle name, then insert a comma and type 2.

Kasper Langmann, Microsoft Office Specialist
  1. Close the parenthesis and press “Enter”.

The last name is extracted😎

The last name is extracted from the first column.

TEXTAFTER is currently only available to Microsoft 365 users😒

Formula example #2 to extract the last name in Excel from the full name column

All Excel users can use the below Excel formula to extract text to the last name column 🤗

  1. Enter an equal sign in the last name column and select the “RIGHT” function.

Write;

=RIGHT(

Enter the RIGHT function in the last name column
  1. Select the full name.

So, choose cell A2.

Now your formula is;

=RIGHT(A2

Selecting the full name from column B
  1. The length of the last name must then be calculated.

How many characters are in the last name? 🤔

We can figure that out using the LEN function and the “Find” function.

LEN function returns how many characters are in a text string.

So,

Number of characters in the last name =

The number of characters in the full name text string – The location of the space character

The number of characters for the last name

Enter the LEN function first, then reference the cell in the full name column.

Now, your formula is;

=RIGHT(A2,LEN(A2)

First step to find number of characters in the last name
  1. Then, reduce the space character position from the above formula.

Use the FIND function to locate the first space in the cell that contains both the first and last names.

Now our formula bar should show:

=RIGHT(A2,LEN(A2)-FIND(” “,A2)

Last step to find number of characters in the last name
  1. Close the parenthesis and press “Enter”.

The last name is extracted 🤩

The last name is extracted using the RIGHT function from the full name column.

You now know how to separate first and last names using Excel formulas.

Let’s look at how to use formulas in Excel to split names when they have first, middle, and last names.

Each full name in the table below has a middle name.

Full names with first, middle, and last names

Let’s learn to split names that have middle names.

Formula 1 to extract names with middle names to separate cells

The easiest function to separate first, last, and middle names in Excel is the TEXTSPLIT function.

  1. Enter an equal sign in column B and select the “TEXTSPLIT”.

Write,

=TEXTSPLIT(

Using TEXTSPLIT function to separate first, middle, and last names
  1. Select the cell from the full name column.

Let’s select A2.

Now your formula is;

=TEXTSPLIT(A2

Selecting the full name from the full name column.
  1. Enter a space character as the delimiter.

Your updated formula should be;

=TEXTSPLIT(A2,” “

The delimiter - Space character
  1. Close the parenthesis and press “Enter”.

First name, middle name, and last name cells are automatically filled 🥳

Get first, middle, and last name using the TEXTSPLIT

TEXTSPLIT removes the need to enter multiple formulas into three columns.

Enter the formula only in one column, column B.

Kasper Langmann, Microsoft Office Specialist

TEXTSPLIT, like TEXTBEFORE and TEXTAFTER, is only available to Microsoft 365 users 💡

Formula example #2 to extract names including middle name

Let’s learn another formula to get the middle name from the full name column.

  1. Enter an equal sign in the middle name column and select “MID”.

Write;

=MID(

Entering MID function
  1. Choose the cell from which to extract the middle name.

Let’s select cell A2.

Now the formula is;

=MID(A2

Select the full name cell.
  1. Then, choose the “middle name” start position.

To do that, locate the first space and then add one.

Basis to find the starting position of the middle name

Now your formula is;

=MID(A2,FIND(” “,A2)+1

Starting position of the middle name
  1. Enter the number of characters for the middle name.

So, we’ll use the formula below to find the last space.

FIND(” “,SUBSTITUTE(A2,” “,”@”,1))-FIND(” “,A2)-1)

Now, your formula should be;

=MID(A2,FIND(” “,A2)+1,FIND(” “,SUBSTITUTE(A2,” “,”@”,1))-FIND(” “,A2)-1

Number of characters of the mid name
  1. Close the parenthesis and press “Enter” to quickly separate the middle name from the full name.
The middle name using the MID formula.

Now you know how to separate names in excel using formulas👏

Split names with Text-to-Columns

Convert text-to-columns wizard is another great Excel feature to convert text to columns.

Let’s apply the convert text to columns wizard to split text to separate columns.

  1. Select the full name column.

Don’t select column headers.

Starting to apply text-to-columns feature
  1. Go to the data tab.
  1. Select “Text to columns” from the “data tools group”
  1. Select “Delimited” and click “Next”.
Text to Columns Wizard - Step 1 of 3
  1. Select “Space option” and click “Next”
Text to Columns Wizard - Step 2 of 3

The data preview window will show how the full name in excel is divided into separate columns.

Kasper Langmann, Microsoft Office Specialist
Data preview - Text-to-columns wizard
  1. Change the destination cell to B2 and click “Finish”.

Otherwise, existing data will be overwritten.

The last step of text-to-columns wizard - Change the destination

Names are now automatically filled into different columns 😜

Complete table - convert text-to-columns wizard

Pro Tip!

With this Excel feature, you can even create a table with just the first and last name in 2 separate columns.

The table to separate first and last name

Repeat the previous steps and make the adjustments listed below in the last step.

  • Select the middle name column from the data preview.
Select the column that you want to skip
  • Select “do not import column” from the “Column data format”
Select the last option of column data format

This is the outcome.

Extract only first and last names.

Split names with Find and Replace

Find and Replace is another great excel feature.

  1. Copy the full name list to a separate name column.
Copy the full name
  1. Open the Find and Replace dialog box.

The keyboard shortcut for “Replace” is “Control + H”.

  1. Enter a space character and an asterisk symbol in the “Find what” and click “Replace All”.
Find and replace -First name
  1. Click OK on the pop-up message.

Then, only the first name will remain in the column, and all remaining names will be erased.

First names with Find and select.

To get the last name in Excel, enter an asterisk symbol and then a space character in the “Find what”.

Kasper Langmann, Microsoft Office Specialist

Split names with Flash Fill feature

This is the simplest way to split names in Excel🤩

Excel senses a pattern and fills in the names with this feature.

  1. Manually type the first name in the first cell.
Manually type first name
  1. Manually enter the first name in the second cell down. As you type, Flash Fill will automatically display a list of first names (in gray).
Flash fill.

Press enter and repeat the same steps for the middle name and last name columns 🥳

That’s it – Now what?

You now know the best ways to separate names in Excel.👏

When names are separated it is easy to apply IF, SUMIF, and VLOOKUP functions to your data.

Click here to access my free 30-minute online course where you can learn about IF, SUMIF, and VLOOKUP if you haven’t already.

Other resources

Our article about LEFT, RIGHT, and MID Functions will help you to separate names using formulas.

Read the article about Find and Replace to learn the way to use it efficiently.

Don’t forget to watch How to Split Cells in Excel – 4 Methods [2022] on YouTube to find out more.

Frequently asked questions

You can use the ampersand(&) method to combine text in Excel.

  1. Type an equal sign.
  2. Select the first name.
  3. Insert an ampersand sign.
  4. Insert a space character within quotation marks.
  5. Insert an ampersand sign again.
  6. Select the next name.

Combining names - Ampersand method

  1. Select the entire column with full names (those you want to split).
  2. Go to the data tab and click the Text to Columns button.
  3. Choose ‘Delimited’ and click ‘Next’
  4. Choose the ‘Space’ option and click ‘Finish’