How to Separate Names in Excel

Today’s tutorial will guide you through several simple steps to see how to separate names in Excel no matter the size of the table you’re working with.

Here we go!

See the video tutorial and transcription below:

See this video on YouTube:

Last time, you could see how to combine names or merge any type of information contained in multiple cells into a single one.

How to Separate Names in Excel - reverse of combine cells

Now we’ll have a look at the same thing but in reverse – we’ll learn how to easily separate information included in one cell into multiple cells.

This data table here will serve as an example,

How to Separate Names in Excel - example of combined names

and we’ll cover how to separate the full name that appears within a single cell into two cells, which means we’ll get a separate first and family (or last) name of an employee.

How to Separate Names in Excel - split name in excel

So, what to do to carry out the task in the simplest way possible and avoid manual typing?

The Simplest Way How to Separate Names in Excel

As usual, in the beginning, you’ll need to select the data you want to separate. Then click on ‘Data’ tab, go to ‘Data Tools’ and choose the function ‘Text to Columns’.

How to Separate Names in Excel - option text to column

Now you’ll see a pop-up window. In most cases, you’ll be good to go with the first option in the window, the option ‘Delimited’. You can use it if you’re working with a table where items are separated with a comma or space.

How to Separate Names in Excel - option delimited

Click on ‘Next’ and we’ll move on.

How to Separate Names in Excel - choose option delimited

The following screen will show us a list of characters which delimit the names in the table. Our delimiter is a comma along with a space, so we’ll tick these two options.

How to Separate Names in Excel - space and comma are delimiters

If there are more than one delimiter, like in our case where we have two, the comma and the space, tick the box ‘Treat consecutive delimiters as one’.

How to Separate Names in Excel - more delimiters

With the box left unselected, the result would appear as it shows here, in the panel at the bottom of the window.

How to Separate Names in Excel - Treat consecutive delimiters as one

Set the design of the final look according to what you need, and let’s carry on.

On the last screen, you can adjust formatting, which we’ll keep set to ‘General’ now.

How to Separate Names in Excel - set formatting

And we can move on.

How to Specify the Location Where Excel Should Place the Separated Names

The last but very important thing is to set the destination. Here you specify the location where Excel should place the separated names.

If you don’t change anything about the default value in ‘Destination’, Excel will be placing the separated names starting in the cell B3. That would overwrite the original information and we’d lose it.

How to Separate Names in Excel - final destination of separated names

Since we want to keep the original data, we’ll specify the cell C3 to be the location where Excel should start placing the separated names.

So, delete the default location and click on cell C3.

How to Separate Names in Excel - define cell in which you want to separate name

Now click on ‘Finish’, then OK and we’re done!

How to Separate Names in Excel - result of dividing names

Remember that these steps can be used with lists of any length, which makes separating names a piece of cake now!

Alternatively, if you want to separate names in Excel, you can use the opposite of the concatenation function covered by Spreadsheet Daddy to achieve the same outcome, only with a lot more flexibility and customization options.

Are you interested in:

If you found this tutorial helpful, give us a Like and watch other video tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button on our YouTube channel and join the EasyClickers!

See you in the next tutorial!