How to Split Cell by Delimiter Using Excel Formula (8 Ways)

This tutorial will show you how to split a cell by delimiter with the help of a formula in Excel. A delimiter is a character that separates chunks of data within the text string. In this article we will demonstrate different ways to split cells by delimiter using formulas in excel.

Before beginning the session, let’s get to know about today’s example workbook.

The base of our example will be students related data (Name, ID, Course, City). Using these data we will show different methods that work in different circumstances.

Examples of all the methods will be stored in separate sheets.

You might need to divide cells in Excel in some circumstances. These could occur when someone copies the information from the internet, a database, or a coworker. If you have entire names and wish to separate them into first and last names, that is a straightforward instance of when you would need to split cells in Excel.

1. Applying Excel Formula with String Functions and SEARCH Function to Split Cell by Delimiter

The first thing you need to do for splitting by delimiter is locate the delimiter itself. Once you locate the delimiter you can easily split from either side of the delimiter. We will use the SEARCH function to locate the delimiter, then we will extract the values from the text using the LEFT, MID, or RIGHT functions.

1.1 Integrating LEFT, & SEARCH Functions

Let’s begin. Since the LEFT function has two parameters, text and number of characters. We will insert the text as we know our text value. For the number of characters, we will use the SEARCH function.

STEPS:

• Firstly, select the cell and put the formula into that cell.

• Further, press the Enter key from your keyboard.

• Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

• Finally, we can see the result.

1.2 Merging MID & SEARCH Functions

Now, Let’s write for the middle value. For this, we will use the MID & SEARCH functions. Let’s follow the procedures below.

STEPS:

• To begin with, select the cell and put in the following formula.

• Press Enter.

• To copy the formula over the range, drag the Fill Handle symbol downward. Alternatively, you can double-click the addition (+) sign to AutoFill the range.

• Lastly, you can see all the middle values are separated now.

1.3 Joining RIGHT, LEN, & SEARCH Functions

Now, to separate the very last cell we will use the combination of RIGHT, LEN, and SEARCH functions. Let’s see the steps down to split the cell by delimiter using the combination of the formula.

STEPS:

• In the first place, choose the cell and insert the following formula into that cell.

• Press the Enter key on your keyboard once more.

• After that, drag the Fill Handle icon to copy the formula over the range. Or, double-click on the plus (+) sign. This also duplicates the formula.

• Thus, the last value will be split by the delimiter.

Read More: How to Split One Cell into Two in Excel

2. Creating Excel Formula for Splitting Cell by Line Break

To split string by line break we will use a similar formula to the previous section. One extra function we need to add to our previous formulas. The function is CHAR.

2.1 Combining LEFT, SEARCH, & CHAR Functions

This CHAR function will supply the line break character. To get the first value and separate it from the cell we will utilize the LEFT, SEARCH, and CHAR functions. Let’s look at the procedures for this.

STEPS:

• Likewise the previous methods, first, choose any cell and put the following formula to extract the topmost value.

• Hit the Enter key to see the outcome.

• Further, by dragging the plus sign you can copy the formula and get the result for the range of cells.

2.2 Adding MID, SEARCH, & CHAR Functions Together

To separate the middle value, let’s see the steps down.

STEPS:

• Similar to the other approaches, first pick any cell and enter the following formula to extract the highest value.

• To view the result, press the Enter key.

• Additionally, you may replicate the formula and obtain the result for the specified range of cells by dragging the plus sign.

2.3 Joining RIGHT, LEN, CHAR, & SEARCH Functions

Now for the right side of the text, our formula will be the combination of the RIGHT, LEN, CHAR, and SEARCH functions. Use the appropriate formula for the rest of the values. So, to separate the bottom value follow the instructions.

STEPS:

• As with the earlier techniques, pick the cell and enter the following formula to extract the bottom value.

• Press Enter key from the keyboard.

• Finally, you may replicate the formula and retrieve the answer for the specified range of cells by dragging the addition sign.

3. Splitting Cell by Text & Number String Pattern in Excel

In this section, we will see how to split a text having a character string followed by a number. For simplicity, we have brought some changes to our sheets (No worries all the sheets will be in the workbook). In our example, we have the Student’s Name and ID together in a column and split them into two different columns.

3.1 Combining RIGHT, SUM, LEN, & SUBSTITUTE Functions

Within SUBSTITUTE we are replacing numbers with space and counting them using LEN. To split text followed by a number format string we need to find out the number first, then with the help of that extracted number we can extract text.

STEPS:

• In the beginning, select the cell where we want to put the result. In our case, we will choose cell C5.
• Then, insert the formula into that cell.

• Hit the Enter key.

• You may also replicate a formula and retrieve the answer for a range of cells by dragging the addition sign.

3.2 Integrating LEFT & LEN Functions

To extract the text value, now we need to use the LEFT function and in the placeholder for the number of characters to provide the total length of the cell length of digits within that. We get the digits from cell D5, as we split the ID in the previous method.

STEPS:

• In the beginning, choose a particular cell and input the formula there.

• Press Enter.

• By dragging the addition sign, you may duplicate a formula and get the result for a group of cells.

Read More: How to Split a Cell into Two Rows in Excel

4. Breaking Cell by Number & Text String Pattern Using Excel Formula

If you have understood the method for splitting a ‘text + number‘, then hopefully, you have begun to imagine a way to split a string of numbers followed by text format. The approach will be the same as earlier, just one change you will notice. Now, the number is at the left of our text, so we need to use the LEFT function to fetch the number and for character text, we will use the RIGHT function.

4.1 Merging LEFT, SUM, LEN, & SUBSTITUTE Functions

To split the cell by number and text string pattern for the topmost value, we need to merge the LEFT, SUM, LEN, and SUBSTITUTE functions.

STEPS:

• Firstly, select the specific cell at the outset and enter the formula there.

• Hit the Enter key.

• In addition, by dragging the addition symbol, you may duplicate a formula and get the result for a group of cells.

4.2 Joining RIGHT & LEN Functions

We need to combine the RIGHT and LEN functions to split the cell by number and text string pattern for the last value.

STEPS:

• To begin, choose the specific cell and enter the formula there.

• Press the Enter button.

• Additionally, you may replicate a formula and obtain the answer for a set of cells by dragging the addition sign.

5. Splitting Date from Cell by Combining RIGHT, LEN, FIND, & SUBSTITUTE Formula

To split the date from your text you can use the combination of the RIGHT, LEN, FIND and SUBSTITUTE functions.

STEPS:

• Choose the desired cell and then type the formula there.

• Further, press the Enter key.

• You may also replicate a formula and obtain the outcome for a set of cells by dragging the addition symbol.

6. Combining Excel FILTERXML & SUBSTITUTE Functions to Split Cell by Delimiter

Using the provided xpath, the FILTERXML function extracts particular data from XML documents. We can combine the FILTERXML and SUBSTITUTE functions to separate cells. Let’s split the cell by delimiter using a formula in Excel.

STEPS:

• Firstly, choose the desired cell and type the formula there.

• Then, hit Enter to see the result.

• Lastly, by dragging the addition sign, you may replicate a formula and obtain the outcome for a collection of cells.

Read More: Excel Formula to Split String by Comma

7. Applying Excel TEXTSPLIT Formula to Split Cell by Delimiter

We use the TEXTSPLIT function where columns and rows are used as delimiters to divide text sequences. You may divide it up by rows or across columns. This is the shortest and simplest way to split any cell by delimiter. To split cell by delimiter using formula in Excel, let’s follow the steps down.

STEPS:

• Choose the cell where you want to see the result, and put the formula there.

• After that, hit Enter.

• Furthermore, you may replicate a formula and obtain the outcome for a set of cells by dragging the addition sign.

8. Split Cells by Merging TRIM, MID, SUBSTITUTE, REPT & LEN Functions

Another combination of the formula is the TRIM, MID, SUBSTITUTE, REPT, and LEN functions, with this we can split cells by delimiter using the formula in Excel.

STEPS:

• Put the formula in the cell where you want to view the outcome after selecting it.

• Then, press Enter.

• By sliding the addition sign, you may also duplicate a formula and get the result for a group of cells.

Read More: How to Split a Single Cell in Half in Excel

How to Split Cell by Delimiter Using Text to Columns Feature in Excel

Excel has a feature within it to split cells. You will find it inside the options of the Data Tab. To use the feature to split cells by delimiter using a formula in Excel, you need to follow the instructions below.

STEPS:

• Firstly, select the cell or column (more often you need to select an entire column).
• Then, explore the Data tab. Here within the Data Tools section, you will find an option called Text to Columns.
• After that, click on that.

• A dialog box will appear in front of you. It’s usual that you will need to split cells by delimiter, so check the Delimited option and click Next.

• Then, you will find an interface containing several delimiters.
• Further, select your preferred one or you can input your own delimiter as well.
• Once you select the delimiter, you will see the result look at the bottom of the box.
• Furthermore, click Next then.

• In this example, we’ve selected a comma here, since our values were comma-separated.
• After clicking Next you will find the options to choose the type of your value and click Finish. You will get a separate value.

• For the time being, we are keeping this as General (by default). The format shown in the image below was produced after making some formations.

Download Practice Workbook

You are welcome to download the workbook from the link below.

Conclusion

The above methods will assist you to split cell by delimiter using formula in Excel. That’s all for today. We have tried listing several ways to split cells by delimiter using formulas. Hope this will be helpful. Feel free to comment if anything seems difficult to understand. You can also let us know any other ways in case we have missed it here.