To separate first name from last name in a list of names in Microsoft Excel, select an empty cell and enter the first name in the list. Then move down to the next cell and type the second name. This will initiate the Flash Fill option. By pressing the Enter key, you can choose names from the list that appears to finish filling out the list. Do this for both first and last names.
There are a few different ways to split names in Excel. Let’s take a look at the different strategies below.
Method 1: Using the Flash Fill Option
Difficulty: Beginner
Time Estimate: 15 Seconds
1. Click on the cell where you want to start your new list and type the first name from the original list.
2. Press the Enter key.
3. Enter the next name in the list, and a list of the names will appear automatically.
4. Again hit the Enter key on your keyboard.
You can fill out your entire list this way. Follow the same pattern for the last names.
Voila! You have successfully separated the names.
Method 2: Using the Data Menu
Difficulty: Beginner
Time Estimate: 15 Seconds
1. Select your data.
2. Go to the Data tab.
3. In the Data Tools menu, pick the Text to Columns option.
4. Choose the Delimited file type.
5. Click Next.
6. Mark Space as the delimiter.
7. Hit Next.
8. Click Finish.
9. Confirm the action by clicking OK.
Here’s what you get! The names have been split into two lists.
Method 3: Using Formulas
Difficulty: Beginner
Time Estimate: 25 Seconds
This approach uses two formulas to separate the first and last names. Let’s consider each in detail.
Formula 1
Separate the first names using the following steps:
1. Click on the cell where you need to paste the first name.
2. Navigate to the Formula bar and enter the formula =LEFT(A1,SEARCH(” “,A1)-1), where A1 is the cell with the full name you need to split.
3. Press the Enter key on your keyboard to get the result.
4. Drag the cell with the result downward through the remaining part of the list.
Easy-peasy! Let’s move on!
Formula 2
Here we will learn how to separate the last names.
1. Select the cell where you want to start the list for the last names.
2. In the Formula bar, type =RIGHT(A1,LEN(A1)-SEARCH(” “,A1)). In this example, A1 contains the full name you want to split.
3. Hit the Enter key.
4. Drag the cell down to copy the formula through the rest of the list.
Just like that, done quickly and easily!
Separating Names in Excel FAQs
If you still have questions, check out the questions and answers below to learn more.
Is there a formula to separate the middle name?
Yes. You just need to choose the cell where you want to insert the result, then go to the Formula bar and enter the following:
=MID(A1, SEARCH(” “, A1) + 1, SEARCH(” “, A1, SEARCH(” “, A1)+1) – SEARCH(” “, A1)-1)
A1 is the cell with the full name you want to split. Press Enter to get the result.
How to combine the first, middle, and last names
To combine the first, middle, and last names which have already been separated, start by selecting the cell where you want the full name. Next, navigate to the Formula bar and type the formula =A1&” “&B1&” “&C. Here, A1 is the cell with the first name, B1 contains the middle name, and C1 is the cell with the last name. Press Enter for the result.
What is the formula to record the initial of the first name?
The formula to record the initial of the first name is =LEFT(B1,1). In our case, B1 is the cell with the first name. Enter this formula in the Formula bar, and you’ll get the result in the selected cell.
How to set all letters to lowercase, uppercase, or proper
Each of these operations has a formula. Start by choosing the cell where you want the answer. Then type the formula you need: =lower(A1) is the formula to apply lowercase letters to the name, =upper(A1) is for uppercase letters, and to set all letters to proper case (capitalizing the first letter only), use =proper(A1). A1 here is the cell with the name. Finally, press Enter on your keyboard.
