Excel formula to separate first and last name with comma

Performing operations on names like search, sort, filter, etc. is usually easier when full names are separated into separate columns.

In fact, separating names is such a frequently performed data processing task, we think Excel should probably introduce a dedicated function for it.

In the meanwhile, however, there is a multitude of ways to separate names in Excel. 

In this tutorial, we are going to show you 5 such methods:

• Using the Text-to-Columns Feature
• Using the Flash Fill feature (Excel 2013 and later versions)
• Using Find and Replace (with wildcard characters)
• Using a formula
• Using Power Query

How to Separate/Split Names in Excel

Let us look at 5 ways to separate names in Excel.

How to separate first and last name in excel - youtube

To demonstrate each method, we will work with the following list of names:

Notice that all the names contain a middle name too.

As you read through the different methods, you will notice that the middle name is usually the most difficult to extract.

However, we have provided detailed steps to help you get the job done quickly and easily.

Method 1: Using the Text-

how to separate first name in excel using formula

how to separate complete name in excel