Having names stuck together in one column can really mess with your data in Excel. Separating them into first, middle, and last name columns makes sorting, filtering, and all sorts of data tasks way easier.
Method 1: Flash Fill
Flash Fill is a super handy feature in Excel (2013 and later versions) that figures out patterns and fills in data for you automatically. It’s a great way to split names without writing complex formulas or manually copying data.
-
Start by adding a new column next to your full names. In the first cell of the new column, manually type the first name from the first full name. So, if cell A2 contains “Steve Rogers,” type “Steve” in cell B2.
-
In the cell below, start typing the first name from the next full name. As you type, Excel will detect the pattern and suggest the rest of the first names in a light gray preview.
-
If the preview looks right, hit
Enterto accept the suggestions. Excel will fill the column with the first names, extracted from the full names.
-
If you also want to extract last names, repeat this process in another new column. Type the last name from the first full name, and Excel will probably offer to fill in the rest again.
You’ll end up with your first and last names in separate columns.
If Flash Fill doesn’t automatically kick in, don’t worry, you can trigger it. After typing the example(s), select the cells where you want the data. Go to the Data tab, and click on Flash Fill in the Data Tools group.
You can also just press Ctrl + E to activate Flash Fill. Excel will fill the cells based on the pattern it recognizes.
If Excel doesn’t get the pattern after your first entry, give it a hand by giving it a few more examples. Type the desired output in the next couple of cells and then use the fill handle to drag down. When you click the Auto Fill Options icon, choose Flash Fill from the menu to populate the rest.
Extracting Middle Names
Flash Fill can also handle extracting middle names, or removing them if you want.
-
To extract middle names, type the middle name of the first full name in a new column. For example, if cell A2 contains “John Fitzgerald Kennedy”, then type “Fitzgerald” in cell B2.
-
Go to the next cell and type the middle name from the next full name. As you type, Excel will probably show a preview of the middle names of the rest. Press
Enterto accept them.
To remove middle names, type the first and last name of the first full name, but without the middle name. Give a couple of examples, and Flash Fill should figure out the pattern to remove middle names from all the other names.
Method 2: Text to Columns Wizard
The Text to Columns feature splits data from one column into multiple columns using a delimiter like a space or a comma.
-
Make sure you have empty columns to the right of your full names so you don’t overwrite any existing data. Then, select the column containing the full names you want to split.
-
Go to the Data tab and click on Text to Columns in the Data Tools group.
-
In the Convert Text to Columns Wizard, select Delimited, and then click Next.
-
Pick the delimiter that separates your data. For names separated by spaces, check the Space option and uncheck any other options. The Data preview will show you how the names will be split. Click Next.
-
In the final step, choose the data format (usually General works fine) and set the destination for the split data. Click in the destination field and choose the first cell where the separated data should be placed. Click Finish.
The full names will now be split into separate columns based on the delimiter you chose.
Note: This method creates static data, so if you change the original names, you’ll need to do it all again to update the separated names.
Splitting Names Separated by Commas
If your names are formatted with commas, such as “Doe, John,” you can use the Text to Columns feature in the same way.
-
Select the column with the full names.
-
Go to the Data tab and click on Text to Columns. Select Delimited and click Next.
-
In the delimiters options, check the Comma box and uncheck any others. The Data preview will show how your names will be split. Click Next.
-
Choose the data format and destination, just like before, and click Finish. The names will be split at the commas into different columns.
Method 3: Using Formulas
Using formulas gives you dynamic results that update automatically when the original data changes. It’s flexible, but takes a little more setup.
Extracting First and Last Names Using Formulas
Get the First Name
To get the first name from a full name in cell A2, use this formula:
=LEFT(A2,FIND(" ",A2)-1)
This formula looks for the space and extracts everything to the left of it.
Copy this formula down to get first names from other full names.
Get the Last Name
To get the last name, use this formula:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
This one calculates the number of characters after the space and grabs them from the right.
Extracting First, Middle, and Last Names Using Formulas
For names with a middle name or initial, you can tweak the formulas a bit.
Get the First Name
The first name formula is still the same:
=LEFT(A2,FIND(" ",A2)-1)
Get the Middle Name
To extract the middle name, use this:
=MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)
This formula finds the first and second spaces to isolate the middle name.
Get the Last Name
And for the last name when there’s a middle name:
=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2)+1))
This one finds the second space and extracts everything after it.
These techniques should help you split up names in Excel, whether you prefer the ease of Flash Fill, the control of Text to Columns, or the dynamic results of formulas.