How to Split a Cell using a Delimiter in an Excel Formula

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

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:

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.

NOTE: You can also split columns. Replace ‘– ’ with another delimiter.

Method 2 – Creating an Excel Formula to Split Cells using a Line Break

2.1 Combining the LEFT, SEARCH, & 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:

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.

3.1 Combining RIGHT, SUM, LEN, & SUBSTITUTE Functions

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

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.

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

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

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.

NOTE: This formula is used when the date is at the end of your text string.

Method 6 – Combining the Excel FILTERXML & SUBSTITUTE Functions to Split a Cell using a Delimiter

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.

Method 7 – Applying the Excel TEXTSPLIT Formula to Split a Cell using a Delimiter

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.

NOTE: You need to have enough empty columns. Otherwise, the #SPILL! error will be displayed.

Method 8 – Split Cells by Merging the TRIM, MID, SUBSTITUTE, REPT & 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