How to Split Data with Formula in Excel (8 Easy Ways)

We can split a cell, a text string, or a column using an Excel formula. This makes the dataset more readable and easily accessible to the correct information. In this article, we are going to see how an Excel Formula is used to split data or strings.

How to Split Data with Formula in Excel: 8 Easy Ways

1. Excel Formula with LEFT & RIGHT Functions to Split Cell

The LEFT function returns the leftmost characters and the RIGHT function helps us to extract the last characters from a text string. Let’s say we have a dataset of range B4:D9 with some random names. We are going to use a formula to split the cells containing those names.

STEPS:

• Select cell C5 at first.
• Then, type the formula.

• Press Enter and use the Fill Handle to see the results in the next cells.

• Select cell D5 and type the formula below.

• In the end, hit Enter and use the Fill Handle tool to see the result.

2. INDEX-ROWS Formula to Split One Column into Multiple Columns in Excel

Excel ROWS function is used to return the row number and the INDEX function returns the value from the given range. We can use the combination of these two functions to split one column into multiple columns. Assuming we have a dataset of range B4:B14. We are going to use the INDEX-ROWS formula to split this column into two columns (Column1 & Column2).

STEPS:

• Write down the formula on cell D5.

• Now press Enter and use the Fill Handle tool to see the result.

• Type the formula on cell E5.

• Hit Enter and use the Fill Handle to autofill the below cells.

3. Excel Formula with Combination of LEFT, MID & RIGHT Functions to Split a Text String

Sometimes we need to split a text string. Microsoft Excel LEFT function returns the leftmost characters of a text string and the RIGHT function helps us to extract the last characters from a text string. On the other hand, the MID function pulls out the middle characters from the middle of a text string. The combination of Excel LEFT, MID & RIGHT functions helps us to split one text string into multiple columns. Here we have a dataset of sold items in range B4:E9. We are going to split the sold item into three columns (CODE, SERIES, NUMBER).

STEPS:

• Move to cell C5.
• Next type the formula below.

• Press Enter and use the Fill Handle tool to the below cells.

• Insert the formula on cell D5.

• Hit Enter and use the Fill Handle to see the result.

• Again select cell E5 and input the fomrula.

• Finally, hit Enter and use the Fill Handle tool to see the result.

4. Excel IF Formula to Split

To run a logical test in a given range, we will use the IF function. It returns the value whether it is TRUE or FALSE. Let’s say we have a dataset of customer payment history in the range B4:F8. We are going to split up the column named AMOUNT into two columns (CASH & CARD).

• In the beginning, select cell E5.
• Next type the formula.

• Press Enter and use the Fill Handle tool to see the result.

This formula will return the AMOUNT value which is paid in Cash in cell E5. Otherwise, it will return N/A.

• Now, select cell F5 and type the formula below.

• At last, press Enter and use the Fill Handle tool to the below cells.

This formula will return the AMOUNT value which is paid in Card in cell F5. Otherwise, it will return N/A.

5. Combination of IFERROR, MID, SEARCH Functions to Split Middle Word

To avoid any error in the formula, we use the IFERROR function as it returns another possible result. Sometimes we have a dataset where each cell contains three words. We can use the MID function to extract the middle word. But if there is no middle word, it will show an error. For that, we use the IFERROR function along with the MID & SEARCH functions to split the middle word in Excel. Suppose we have a dataset containing different writers’ names.

STEPS:

• First, select cell D5 and insert the formula below.

• In the end, press Enter and use the Fill Handle tool to the below cells.

6. Excel Formula with SUBSTITUTE Function to Split Date

To replace a specific character in a given range with another, we use the SUBSTITUTE function. We can use an Excel formula with SUBSTITUTE, LEN & FIND functions wrapped in the RIGHT function to split the date from the cell. We have to remember that the formula can be used only when there is a date at the end of the cell like the below dataset (B4:C8).

STEPS:

• Write down the formula on cell C5.

• Finally, hit Enter and use the Fill Handle tool to autofill the cells.

7. Excel Formula to Split Text Using CHAR Function

Excel CHAR function is a Text function. It means character. It returns a character that is specified by the ASCII code number. We can use the CHAR function to split text by line break as this function supplies the break character. Assuming we have a dataset (B4:C8) of Microsoft product names with year. We are going to extract the product name using the CHAR & SEARCH functions wrapped into the LEFT function. Here the ASCII code for the line is 10.

STEPS:

• Type the formula on cell C5.

• Then hit Enter and use Fill Handle to see the result.

8. FILTERXML Formula to Split in Excel

To see the output text as a dynamic array after splitting, we can use the Excel FILTERXML function. It is available in Microsoft Office 365. Let’s say we have a dataset (B4:B8) of customers’ payment history. We are going to split the customer names and the payment methods.

STEPS:

• Select Cell C5.
• Next, write down the formula.

Here the sub-node is represented as s and the main-node is represented as t.

• Then press Enter and use Fill Handle to autofill the below cells.

Practice Workbook

Download the following workbook and exercise.

Conclusion

These are the quickest ways to use Excel Formula to split. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

<< Go Back to Split in Excel | Learn Excel