How to Split First And Last Name in Excel (6 Easy Ways)

In Excel, you can split your full name into separate columns. There are a couple of ways to split first name and last name. You can use Excel inbuilt features as well as different formulas to split a full name into first and last names. In this article, I’m going to explain how to split first and last name in Excel.

To make the demonstration more understandable, I’m going to use a sample dataset of a particular bank. The dataset contains two columns that are Full Name and Account Number.

Download to Practice

6 Ways to Split First And Last Name in Excel

1. Using Text to Columns to Split First And Last Name

You can use the Text to Columns feature to Split First and Last Name.

To begin with, select the cell or cell range from where you want to Split First and Last Name.

➤ I selected the cell range B4:B13.

Now, open the Data tab >> from Data Tools >>  select Text to Columns

A dialog box will pop up. From there Choose the file type that best describes your data.

⏩ I selected Delimited as my data has space character.

Then, click Next.

Another dialog box will pop up. From there select the Delimiters that your data has.

⏩ I selected Space as my data has space characters.

Then, click Next.

Again, another dialog box will pop up. From there select the Destination to place your split data.

⏩ I selected the D4 cell to place the separated first and last names.

Finally, click Finish.

⏩ A warning message will appear.

Click, OK if you are 100% sure to place the separated texts.

⏩ Hence, you will get the first and last name from the full name.

2. Using Flash Fill to Split First And Last Name

You also can use the Flash Fill feature to Split First and Last Name.

While using the Flash Fill command you will need to create a pattern. If Excel detects the pattern then it Fills the data automatically.

As I want to split first and last name I added two new columns one for First Name another for Last Name.

Now, type the name part of the Full Name that you want to extract in the first cell.

➤ I typed the name Adam in the First Name column.

➤ Then, in the second cell type the first name of the B5 cell. In most cases, Excel senses a pattern, if the pattern is detected then it will populate the first names in all other cells automatically.

Hence, you can see that Excel detected the pattern and showed all the First Names.

⏩ Now, you will have to press ENTER to Fill all the First Names automatically.

Follow the procedure explained for the First Name to split the Last Name from the Full Name.

Then, you will see that Excel detected the pattern and showed all the Last Names.

⏩ Now, press ENTER to Fill all the Last Names automatically.

In case your Flash Fill feature is not enabled by default. Then, to use the Flash Fill feature click on the Data tab >> from the Data Tools group >> select Flash Fill.

If it still doesn’t work, then go to File >> open Options >> go to Advanced >> select Flash Fill box (Also make sure the Automatically is selected under Editing options)

3. Using Flash Fill to Split First And Last Name When Middle Name Exists

In case your Full Name contains the First, Last, and Middle names you will be able to use the Flash Fill command to Split First and Last Name ignoring the Middle name.

To demonstrate the procedure, I’ve taken a dataset given below that contains First, Last, and Middle names.

As I want to split first and last name I added two new columns one for First Name another for Last Name.

Now, type the name part of the Full Name that you want to extract in the first cell.

➤ I typed the name Adam in the First Name column.

Instead of using the default Flash Fill option, I will use the Flash Fill feature from the ribbon.

To start with, first, select the cell where you stirred the pattern to follow.

➤ I selected cell D4.

Now, open the Data tab >> from the Data Tools group >> select Flash Fill

⏩ Therefore, you will get the First Name from the Full Name.

Again, I selected cell E4 to split Last Name from Full Name while ignoring Middle Name.

Now, open the Data tab >> from the Data Tools group >> select Flash Fill

⏩ As a result, you will get the Last Name from the Full Name.

Read More: Splitting Text in Excel Using Flash Fill

4. Using Functions to Split First And Last Name

To Split First and Last Name, you can use Excel functions. You can use the LEFT function, RIGHT function with the FIND function to separate First Name and Last Name from Full Name when the names are separated with space characters.

4.1. Using LEFT & FIND Function to Split First Name

By using the LEFT function and the FIND function, you can split the First Name from the Full Name.

To start with, select any cell to place the First Name.

➤ I selected the D4 cell.

⏩ In cell D4, type the following formula.

Here, in the LEFT function, I selected the cell B4 as text and FIND(” “,B4,1)-1 as  num_chars.

Next, in the FIND function, I used ” ” (space) as find_text, selected cell B4 as within_text, and used 1 as start_num.

⏩ Press the ENTER key and you will get the First Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Separate Words in Excel Using Formula (Ultimate Guide)

4.2. Using RIGHT & FIND Function to Split Last Name

To split the Last Name from the Full Name you can use the RIGHT function with the FIND function and the LEN function.

To start with, select any cell to place the Last Name.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

Here, in the RIGHT function, I selected the cell B4 as text and LEN(B4)-FIND(” “,B4,1) as  num_chars.

Next, in the LEN function, I selected the B4 cell as text to get the length of the value of the B4 cell.

Then, in the FIND function, I used ” ” (space) as find_text, selected cell B4 as within_text, and used 1 as start_num.

⏩ Press the ENTER key and you will get the Last Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

5. Using Function to Split First And Last Name with Comma

In case you have Full Name separated with a comma then you also can use the Excel functions to Split First and Last Name.

To demonstrate the procedure, I’m going to use the dataset given below where I separated names with commas.

5.1. Using LEFT & SEARCH Function to Split First Name

You can use the LEFT function and the SEARCH function to split the First Name from the Full Name.

To start with, select any cell to place the First Name.

➤ I selected the D4 cell.

⏩ In cell D4, type the following formula.

Here, in the LEFT function, I selected the cell B4 as text and SEARCH(” “,B4)-2 as  num_chars. Here, I subtracted 2 as I have two extra characters (comma & space).

Next, in the SEARCH function, I used ” ” (space) as find_text, selected cell B4 as within_text.

⏩ Press the ENTER key and you will get the First Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

5.2. Using RIGHT & SEARCH Function to Split Last Name

To separate the Last Name from the Full Name you can use the RIGHT function with the SEARCH function and the LEN function.

To start with, select any cell to place the Last Name.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

Here, in the RIGHT function, I selected the cell B4 as text and LEN(B4) – SEARCH(” “, B4) as num_chars.

Next, in the LEN function, I selected the B4 cell as text to get the length of the value of the B4 cell.

Then, in the SEARCH function, I used ” ” (space) as find_text, selected cell B4 as within_text.

⏩ Press the ENTER key and you will get the Last Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

6. Using Find and Replace to Split First And Last Name

You can use the Find & Replace feature with wildcard characters to Split First and Last Name.

6.1 Find The First Name

By using the Replace from Find & Select feature you can extract the First Name from the Full Name.

To begin with, copy all the names from Full Name to any new column.

➤ I selected the range B4:B13 and copied all the data to the First Name column.

Next, select the cell range from which you want to extract only your First Name.

➤ I selected the cell range D4:D13.

Then, open the Home tab >> from Editing group >> go to Find & Select >> select Replace

A dialog box will pop up.

⏩ I used single Space then Asterisk(*) in Find what because I only want the values before space.

⏩ I kept the Replace with field Blank.

Then, click on Replace All.

A message will pop up showing how many replacements occurred.

⏩ We made 10 replacements.

Then, click OK and close the dialog box.

⏩ Here, all the characters after space are replaced with Blank and only find the First Name.

6.2. Find The Last Name

You also can use the Replace from Find & Select feature to extract the Last Name from the Full Name.

To begin with, copy all the names from Full Name to any new column.

➤ I selected the range B4:B13 and copied all the data to the Last Name column.

Next, select the cell range from where you want to extract only Last Name.

➤ I selected the cell range E4:E13.

Then, open the Home tab >> from Editing group >> go to Find & Select >> select Replace

A dialog box will pop up.

⏩ I used Asterisk(*) then single Space in Find what because I only want the values after space.

⏩ I kept the Replace with field Blank.

Then, click on Replace All.

A message will pop up showing how many replacements occurred.

⏩ We made 10 replacements.

Then, click OK and close the dialog box.

⏩ Here, all the characters before space are replaced with Blank and you will get the Last Name.

Things to Remember

🔺 You only can use the Flash Fill feature in Excel 2013, 2016, 2019, and later versions.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Conclusion

In this article, I have explained 6 ways to split first and last name in Excel. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• How to Split a String into an Array in VBA (3 Ways)
• Split String by Character in Excel (6 suitable Ways)
• How to Split String by Length in Excel (8 Ways)
• Separate Two Words in Excel (6 Easy Ways)