How to Separate All Sorts of Data Using Excel Formulas

When it comes to data management and organization, splitting text is the one task that’s inevitable.

It can range from splitting numbers from text to parsing out data based on specific delimiters. Luckily, Excel offers a variety of methods you can use to separate different types of data 😉

Knowing how to leverage Excel formulas to achieve the same result as built-in features adds a plus one to your skill set and makes your workflow more efficient and streamlined.

In this tutorial, we will see different formulas you can use to split names and extract addresses from your database. Download our sample workbook here to practice along the guide.

Table of Contents

Separate first and last name

Separating first and last names in a data set is pretty easy. We will use the LEFT, RIGHT, and FIND functions for this purpose. Let’s see how to separate names in a database below 🔽

We will use the following sample data set.

To separate the first name,

Step 1) Select cell B1 and type in your First Name.

Step 2) In cell B2, type the following formula:

Step 3) Press Enter.

Step 4) Double-click the Fill Handle to copy the formula down to the remaining cells.

All first names from the data set appear in the selected column 😀

To extract the last name,

Step 5) Select cell C1 and type in Last Name.

Step 6) In cell C2, type the following formula:

Step 7) Press Enter.

Step 8) Double-click the Fill Handle to copy the formula down to the remaining cells.

The last names in your data set will appear in a new column.

Wasn’t that easy? Try with your data set now! 😀

Separate data based on delimiter

You can combine different functions in Excel to achieve a certain purpose. For separating data based on a delimiter, we will use the TEXTSPLIT function. Let’s see how it’s done below!

We will use the following sample data set where addresses are separated by commas 😎

To do that,

Step 1) Make sure the columns next to your current selection are empty – if not – insert columns corresponding to the number of elements you want to separate.

Step 2) Label each column according to the data to be filled in it.

Step 3) In cell B2, type in the following formula:

Step 4) Press Enter.

The formula will spill output to the adjacent columns. Double-click the fill handle to copy the formula down to the remaining cells 📃

For older versions, use the following formulas:

1. Name:

       2. Extension:

       3. Mail:

       4. Copy the formula down the remaining cells using Fill Handle.

And tada! All the contents of column A are separated by the selected delimiter.

Wasn’t that easy? 😃

Separate text and numbers

Excel lets you combine formulas to separate text and numbers. There are two different ways of achieving the same result. Let’s see how they work below.

We will use the following sample data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formulas:

Step 3) Press Enter.

This formula will return the text part of the dataset in the adjacent empty column 🤩

To extract the numbers in a separate column,

Step 4) Type in the following formula in cell C2.

Step 5) Press Enter.

The numbers in the data set will be separated 😉

However, if you are using an older version of MS Excel, you can use the following formula.

Step 1) Select an empty cell next to the data set.

Step 2) Type the following formula to separate text:

Step 3) Press Enter.

This will separate the text.

To separate numbers,

Step 4) Use the following formula:

Step 5) Press Enter.

The numbers in the data set will be separated.

And just like that, all your data has been separated. How cool is that? 🤠

Separate special characters

Separating data sets based on special characters is pretty simple. Let’s see how to do that below.

We have the following sample data set.

To do that,

Step 1) Select an empty cell next to your dataset.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula will spill the separated data set upon encountering the special character 🧐

To get the same results in MS Excel 2021 or older versions,

Step 1) Select the cell next to the data set.

Step 2) Type in the following formula:

Step 3) Press Enter.

This will spill the data before the special character in a new column.

To separate the data after the special character,

Step 4) Select cell C2.

Step 5) Type the following formula:

Step 6) Press Enter.

The data after the special character will be separated.

Both the formulas are generic and work for all special characters. This way you don’t need to specify a character each time in the formula.

How easy was that? Try it now! 🙌

Separate date and time

Similar to other data types above, we can also separate date and time in a data set. Let’s see how to do that below.

We have the following sample data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The date and time from the data set will be separated into different columns 📅

To use a different approach,

Step 1) Select a cell next to your data set.

Step 2) Type in the following formula:

Step 3) Press Enter.

This will extract the date from the data set.

To extract the date,

Step 4) Select cell C2.

Step 5) Type in the following formula:

Step 6) Press Enter.

The data in the data set will be separated into different columns.

Cool, no? 😉

Conclusion

In this guide, we saw how to separate data of all sorts in Excel. We saw how to extract numbers from text, split names, and more 😀

We used different formulas like LEFT, RIGHT, MID, FIND, etc. It might take you a little time working with the first formula but once you get a hold of it, you’ll be able to tackle any data separation task.

Excel’s versatility ensures you can adapt any of these methods to suit your specific needs and make accurate and insightful data analysis easier.

To learn more about data separation in Excel, give the following articles a read:

How to Split Text in Formula (TEXTSPLIT Function)

How to Separate Names in Excel (Split First and Last)

How to Separate Date and Time in Excel (Example)

We hope you enjoy reading this as much as we did crafting this! 😊