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.

`=LEFT(B5, SEARCH("-",B5,1)-1)`

- 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.

**🔎**** How Does the Formula Work?**

In the example, our delimiter is the hyphen â€˜**–**â€™. The **SEARCH** function would have provided us with the position of a hyphen. Now, we donâ€™t need the hyphen itself, we need to extract it ahead of the hyphen.

**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.

`=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)`

- 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.

**🔎**** How Does the Formula Work?**

The location of one text string inside another is returned by the **SEARCH **function. It will start from the character next to the hyphen. Based on the number of characters we provide, **MID** retrieves a certain number of characters from a text string, beginning at the place you designate.

**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.

`=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))`

- 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.

**🔎**** How Does the Formula Work?**

Here, the **LEN** function returns the total length of the string, from which we subtract the position of the last hyphen. The **SEARCH** function would have provided us with the position of a hyphen. Then, the difference is the number of characters after the last hyphen, and the **RIGHT** function extracts them.

**NOTE:**You can split columns by any other character in a similar fashion. All you need to do is to replace â€˜

**–**â€™ with your required 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.

`=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)`

- 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.

**🔎**** How Does the Formula Work?**

**10** is the **ASCII** code for the line. We are providing 10 within **CHAR** to search line breaks. A character that is determined by a number is returned. Further, it searches for the break. After that, this returns the topmost value.

**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.

`=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)`

- 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.

`=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))`

- 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.

`=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))`

- Hit the
**Enter**key.

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

**🔎**** How Does the Formula Work?**

To extract the numbers, we need to look for every possible number from **0** to **9** within our string. Then, get the total of the number and return the number of characters from the end of the string.

**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.

`=LEFT(B5,LEN(B5)-LEN(D5))`

- 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.

`=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))`

- 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.

`=RIGHT(B5,LEN(B5)-LEN(C5))`

- 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.

`=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))`

- 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.

**🔎**** How Does the Formula Work?**

As the date value is at the end of the string, we have traversed a number of instances so that month, date, and year can be abstracted. If your target value requires more text to drive, you can extract them by changing the number of instances.

**NOTE:**This formula will only be useful when you have a date at the end of your text string.

**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.

`=FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s[2]")`

- 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.

**🔎**** How Does the Formula Work?**

Here, the **SUBSTITUTE** is to replace specific text in a text string. Then, Excel’s **FILTERXML** function enables you to pull data from an **XML** file.

**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.

`=TEXTSPLIT(B5,",")`

- After that, hit
**Enter**.

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

**NOTE:**Make sure you have enough empty columns beside it. Otherwise, you may face the

**#SPILL!**error.

**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.

`=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))`

- Then, press
**Enter**.

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

**🔎**** How Does the Formula Work?**

Here, **LEN** returns the length of a text string in characters. Then, the **SUBSTITUTE** function replaces text that appears at a certain spot in a text string. After that, the** MID **function gives a certain number of words from a text string, beginning at the place you designate. Finally, the **TRIM** function removes all white space from the text with the exception of double spaces afterwords.

**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**tabsection, you will find an option called*Data Tools***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
you will find the options to choose the type of your value and click*Next***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.

Get FREE Advanced Excel Exercises with Solutions!