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.

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:

Click to copy

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 😀

The logic of this function is that we use the FIND function to list the number of characters before space in cell A2. The output of that function is used as input for the LEFT function – it uses the number of characters starting from the left of the cell to display the name.

To extract the last name,

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

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

Click to copy

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.

In this formula, we use the LEN function to find the length of the cell contents. From that length, we subtract the number of characters before space using FIND. That leaves us with the number of characters in the last name. Using the RIGHT function, we extract the characters of the last name and display it in the cell.

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:

Click to copy

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 📃

Note that the TEXTSPLIT function is only available in Microsoft 365. In older versions, a better alternative is to use the Text-to-columns feature or combine the LEFT, FIND and RIGHT functions to extract text with a delimiter.

For older versions, use the following formulas:

1. Name:
Click to copy

2. Extension:

Click to copy

3. Mail:

Click to copy

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:

Click to copy

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.

Click to copy

Step 5) Press Enter.

The numbers in the data set will be separated 😉

The logic of these formulas is such that each element in the array is a delimiter. When the formula encounters any matching element in the data set, it splits the data at that point and copies cells with similar data types to create a new column.

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:

Click to copy

Step 3) Press Enter.

This will separate the text.

To separate numbers,

Step 4) Use the following formula:

Click to copy

Step 5) Press Enter.

The numbers in the data set will be separated.

The above formulas use a combination of functions to check each character in the string and separate text and number based on the type of character. The final result is then compiled into a single string.

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:

Click to copy

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:

Click to copy

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:

Click to copy

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:

Click to copy

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:

Click to copy

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:

Click to copy

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.