Shakil Ahmed
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.
Download Practice Workbook
You are welcome to download the workbook from the link below.
8 Different Ways to Split Cell by Delimiter Using Formula in Excel
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. Combine Excel String Functions with SEARCH Function to Split by Dash/Hyphen Delimiter Separated Text
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. Integrate 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. Merge 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. Compound 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.
Read More: Excel Formula to Split: 8 Examples
2. Merge Formulas for Splitting Text 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. Combine 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. Add 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. Join 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.
Read More: Excel Formula to Split String by Comma (5 Examples)
3. Split 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 Student’s Name and ID together in a column and split them into two different columns.
3.1. Combine 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 by 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 numbers total and return the number of characters from the end of the string.
3.2. Integrate 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. And 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 (3 ways)
4. Break Cell by Number & Text String Pattern Using 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. Merge 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. Compound 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. Split Date from Cell by Combining RIGHT, LEN, FIND, & SUBSTITUTE Functions
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 so 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.
Read More: Excel VBA: Split String into Cells (4 Useful Applications)
6. Combine FILTERXML & SUBSTITUTE Functions to Split Cell
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 cell by delimiter using 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: How to Split One Cell into Two in Excel (5 Useful Methods)
7. Apply TEXTSPLIT Function to Break Cells 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.
8. Split Cells by Combining 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 after words.
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.
Conclusion
The above methods will assist you to split cell by delimiter using formula in Excel. That’s all for today. With the above methods, you can split cell by delimiter using formula in Excel. 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.
