A delimiter is a character that separates data within the text string.
The dataset showcases students’ Name, ID, Course and City.
The examples below will be stored in separate sheets.
Method 1 – Applying an Excel Formula with the String and SEARCH Functions to Split a Cell using a Delimiter
Use the SEARCH function to locate the delimiter, and extract the values from the text using the LEFT, MID, or RIGHT functions.
1.1 Using the LEFT, & SEARCH Functions
STEPS:
- Select a cell and enter the formula.
=LEFT(B5, SEARCH("-",B5,1)-1)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Formula Breakdown
The delimiter is the hyphen ‘–’. The SEARCH function returns the position of the hyphen and extracts the value after it.
1.2 Merging the MID & SEARCH Functions
STEPS:
- Select a cell and enter the formula.
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- The middle values are separated.
Formula Breakdown
The location of one text string inside another is returned by the SEARCH function. It starts from the character next to the hyphen. Based on the number of characters provided, MID retrieves a number of characters from a text string.
1.3 Joining the RIGHT, LEN, & SEARCH Functions
To separate the last cell, use the combination of the RIGHT, LEN, and SEARCH functions.
STEPS:
- Select a cell and enter the formula.
=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- The last value is split by the delimiter.
Formula Breakdown
The LEN function returns the total length of the string, from which the position of the last hyphen is subtracted. The SEARCH function returns the position of the hyphen. The difference is the number of characters after the last hyphen: the RIGHT function extracts them.
Read More: How to Split One Cell into Two in Excel
Method 2 – Creating an Excel Formula to Split Cells using a Line Break
Use the CHAR Function.
2.1 Combining the LEFT, SEARCH, & CHAR Functions
The CHAR function supplies the line break character. To get the first value and separate it from the cell, use the LEFT, SEARCH, and CHAR functions.
STEPS:
- Select a cell and enter the formula.
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
10 is the ASCII code for the line. 10 is provided within CHAR to search line breaks. A character that is determined by a number is returned. It searches for the break and returns the topmost value.
2.2 Adding MID, SEARCH, & CHAR Functions Together
To separate the middle value:
STEPS:
- Select a cell and enter the formula.
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
2.3 Joining the RIGHT, LEN, CHAR, & SEARCH Functions
To separate the right side of the text, combine the RIGHT, LEN, CHAR, and SEARCH functions.
STEPS:
- Select a cell and enter the formula.
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 3 – Splitting Cells using the Text & Number String Pattern in Excel
The dataset showcases Student’s Name and ID in a column. To split them into two different columns:
3.1 Combining RIGHT, SUM, LEN, & SUBSTITUTE Functions
Within the SUBSTITUTE Function, replace numbers with a space and count them using the LEN function. Find and extract the number and the text.
STEPS:
- Select a cell and enter the formula. Here, C5.
=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
To extract numbers, look for every possible number from 0 to 9 within the string. Get the total and return the number of characters from the end of the string.
3.2 Combining the LEFT & LEN Functions
To extract the text value, use the LEFT function and in the placeholder for the number of characters, provide the total length of the cell. Get the digits from D5.
STEPS:
- Select a cell and enter the formula.
=LEFT(B5,LEN(B5)-LEN(D5))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Split a Cell into Two Rows in Excel
Method 4 – Breaking a Cell using the Number and Text String Pattern in an Excel Formula
4.1 Merging the LEFT, SUM, LEN, & SUBSTITUTE Functions
To split the cell by number and text string pattern for the topmost value, merge the LEFT, SUM, LEN, and SUBSTITUTE functions.
STEPS:
- Select a cell and enter the formula.
=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
4.2 Joining the RIGHT & LEN Functions
STEPS:
- Select a cell and enter the formula.
=RIGHT(B5,LEN(B5)-LEN(C5))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 5 – Splitting a Date in a Cell by Combining the RIGHT, LEN, FIND, & SUBSTITUTE Formulas
To split the date, use the combination of the RIGHT, LEN, FIND and SUBSTITUTE functions.
STEPS:
- Select a cell and enter the formula.
=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
As the date value is at the end of the string, traverse a number of instances so that month, date, and year can be extracted.
Method 6 – Combining the Excel FILTERXML & SUBSTITUTE Functions to Split a Cell using a Delimiter
Using the provided xpath, the FILTERXML function extracts data from XML documents. Combine the FILTERXML and SUBSTITUTE functions to separate cells.
STEPS:
- Select a cell and enter the formula.
=FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s[2]")- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
The SUBSTITUTE Function replaces a specific text in a text string. The FILTERXML function pulls data from an XML file.
Read More: Excel Formula to Split String by Comma
Method 7 – Applying the Excel TEXTSPLIT Formula to Split a Cell using a Delimiter
Use the TEXTSPLIT function: columns and rows are used as delimiters to divide text sequences.
- Select a cell and enter the formula.
=TEXTSPLIT(B5,",")- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 8 – Split Cells by Merging the TRIM, MID, SUBSTITUTE, REPT & LEN Functions
Combine the TRIM, MID, SUBSTITUTE, REPT, and LEN functions.
STEPS:
- Select a cell and enter the formula.
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
The LEN Function returns the length of a text string in characters. The SUBSTITUTE function replaces text in a text string. The MID function returns a number of words from a text string, beginning at the assigned place. The TRIM function removes all white spaces from the text
.
Read More: How to Split a Single Cell in Half in Excel
How to Split a Cell with a Delimiter Using the Text to Columns Feature in Excel
STEPS:
- Select a cell or column.
- In the Data tab, select Data Tools.
- Choose Text to Columns.
- In the dialog box, check Delimited and Next.
- Select a delimiter.
- Click Next.
- Here, a comma was selected.
- Click Next.
- Choose a type of value and click Finish. You will get a separate value.
- Keep the format as General (by default) or choose format options.
Download Practice Workbook
Download the workbook.
Get FREE Advanced Excel Exercises with Solutions!
