Pinterest Pixel

How to Separate Names in Microsoft Excel Easily

You’re tasked to send out invites to the company barbecue based on the employees’ first names. To... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Separate Names in Microsoft Excel Easily | MyExcelOnline How to Separate Names in Microsoft Excel Easily | MyExcelOnline


You’re tasked to send out invites to the company barbecue based on the employees’ first names. To do this, you’re going to have to know how to split data such as names into different columns. In this article, we will guide you through the process of How to Separate Names in Excel, using various methods depending on your needs:

Let’s look at these methods thoroughly!

Download the Excel Workbook below to follow along and understand How to Use Substrings in Microsoft Excel

download excel workbookSplit-Names.xlsx

Using Text to Columns

Excel’s Text to Columns is a built-in tool that can quickly split names based on a chosen delimiter, such as a space or a comma. To use this feature, follow these steps:

Open your spreadsheet and select the cells in the column containing the full names that you want to split.

How to Separate Names in Microsoft Excel Easily

Go to the Data tab and click on the Text to Columns button. This will open the Convert Text to Columns Wizard dialog box. Choose the Delimited option if your names are separated by a specific character like a space or a comma.

How to Separate Names in Microsoft Excel Easily

If you select the Delimited option, specify the delimiter that separates your names. In our case, check the Space option because the names are separated by a space. (If the names are separated by a comma, check the comma checkbox) Make sure the preview looks correct.

See also  2 Best Ways to Use the IF ELSE Function in Excel (Examples)

How to Separate Names in Microsoft Excel Easily

Choose the format for each column. If you only want to separate the names and don’t need any additional formatting, you can leave the default options as they are.

How to Separate Names in Microsoft Excel Easily

Click Finish.

Excel will split the names into separate columns based on your chosen delimiter.

How to Separate Names in Microsoft Excel Easily

Note: It is important for the columns to the right to be empty in order for the new columns to have a destination.

Using Formulas

The formula method is a more flexible approach if you have specific requirements for separating names. Follow these steps to use the LEFT, RIGHT, FIND, LEN functions:

LEFT formula

Insert two new columns next to the column containing the full names. These columns will hold the first names and last names, respectively.

In the first cell of the first name column, enter the following formula:

=LEFT(A2, FIND(” “, A2)-1)
How to Separate Names in Microsoft Excel Easily

This formula uses the LEFT function to extract characters from the left side of the cell value and the FIND function to locate the position of the space character that separates the first and last names. Pull down on the bottom right-hand corner of cell B2 to apply the formula to the rest of the cells.

See also  How to Create A Yearly Leave Record for Employees in Excel - The Easy Way

RIGHT formula

In the first cell of the last name column, enter the following formula:

=RIGHT(A2, LEN(A2)-FIND(” “, A2))
How to Separate Names in Microsoft Excel Easily

This formula uses the RIGHT function to extract characters from the right side of the cell value and the FIND function to locate the position of the space character. Pull down on the bottom right-hand corner of cell C2 to apply the formula to the rest of the cells.

How to Separate Names in Microsoft Excel Easily

The LEN function calculates the length of the cell value, and the subtraction determines the number of characters to extract from the right side.

By using these formulas, you can customize the splitting process further. For example, if your names are separated by a comma instead of a space, you can modify the formulas accordingly.

Using Flash Fill

Flash Fill offers a simple and interactive way to split names in Excel without the need for complex formulas or additional tools.

Follow these steps if you prefer a straightforward solution for basic name separation:

See also  Generate Random Numbers: 2 Quick and Practical Ways in Excel

Ensure that you have a column containing the full names that you want to split. In our case, the full names are in Column A.
How to Separate Names in Microsoft Excel Easily

In a new column next to the column with the full names, enter the desired format for the separated names. For example, if you want to separate the full names into first name and last name, enter the desired format in the adjacent column. In this case, you can enter the first name of the first person manually.
How to Separate Names in Microsoft Excel Easily

Select all the cells underneath cell B2. Go to the Data tab in the Excel ribbon, select the Flash Fill Button. Excel will detect the pattern based on your input and will automatically fill in the separated names for the remaining cells based on the pattern it recognized. Do this for the last name column, as well.

How to Separate Names in Microsoft Excel Easily

Once you are done separating the names, you can remove the column containing the original full names if it is no longer needed.

See also  How to Use YYYY-MM-DD Date Format in Excel - Step by Step Guide

There you have it! Excel is an incredibly powerful tool for splitting names. When you need to separate first names and last names for further analysis, Excel provides various functions and techniques to accomplish this task efficiently.

If you like this Excel tip, please share it
How to Separate Names in Microsoft Excel Easily | MyExcelOnline How to Separate Names in Microsoft Excel Easily | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...