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.

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

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

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

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

**Read More: **Excel Formula to Split String by Comma

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

.

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