How to☝️ Separate Names in Excel

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.

Using the Flash Fill Option

4. Again hit the Enter key on your keyboard.

The data

You can fill out your entire list this way. Follow the same pattern for the last names.

The list of 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.

Using the Data Menu

4. Choose the Delimited file type. 

5. Click Next.

Delimited file type

6. Mark Space as the delimiter.

7. Hit Next.

Space as the delimiter

8. Click Finish.

Data Preview

9. Confirm the action by clicking OK.

Confirm the action by clicking OK

Here’s what you get! The names have been split into two lists.

Names

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.

=LEFT(A1,SEARCH( ,A1)-1) formula

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.

Names in Excel

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.

=RIGHT(A1,LEN(A1)-SEARCH( ,A1)) formula

3. Hit the Enter key.

4. Drag the cell down to copy the formula through the rest of the list.

List of the names in Excel

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.

MID formula

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.

How to combine the first, middle, and last names

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.

What is the formula to record the initial of the first name

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.

How to set all letters to lowercase, uppercase, or proper
More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X