Excel formula helps us to split a cell or a text string or a column. This makes the dataset more readable and easily accessible to the correct information. In this article, we are going to see **how an Excel Formula is used to split cells** or strings.

**Table of Contents**hide

**Practice Workbook**

Download the following workbook and exercise.

## 8 Easy Ways to Apply a Formula to Split in Excel

### 1. Excel Formula with LEFT & RIGHT Functions to Split Cell

The **LEFT ****function **returns the leftmost characters and the **RIGHT function** helps us to extract the last characters from a text string. These are Microsoft Excel **Text Functions**. Let’s say we have a dataset (**B4:D9**) with some random names. We are going to use a formula to **split the cells** containing those names.

**STEPS:**

- Select
**Cell C5**at first. - Now type the formula:

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

- Then press
**Enter**and use the**Fill Handle**to see the results in the next cells.

**Formula Breakdown**

**➤**** SEARCH(” “,B5)**

This will search for the space and return with the position of the space with the **SEARCH function**.

**➤**** LEFT(B5,SEARCH(” “,B5)-1)**

This will extract all the characters on the left and return the value.

- Next select
**Cell D5**. - Type the formula:

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

- In the end, hit
**Enter**and use the**Fill Handle**tool to see the result.

**Formula Breakdown**

**➤**** SEARCH(” “,B5)**

This will search for the space and return with the position of the space with the **SEARCH function**.

**➤**** LEN(B5)**

This will return the total number of characters with the **LEN function**.

**➤**** RIGHT(B5,LEN(B5)-SEARCH(” “,B5))**

This will return the last name value.

**Read More: How to Split Cells in Excel (The Ultimate Guide)**

### 2. INDEX-ROWS Formula to Split One Column into Multiple Columns in Excel

Excel **ROWS function** is used to return the row number and the **INDEX function** returns the value from the given range. We can use the combination of these two functions to split one column into multiple columns. Assuming we have a dataset (**B4:B14**). We are going to use the INDEX-ROW formula to split this column into two columns (**Column1 **& **Column2**).

**STEPS:**

- First select
**Cell D5**. - Next, write down the formula:

`=INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)`

- Now press
**Enter**and use the**Fill Handle**tool to see the result.

**Formula Breakdown**

**➤**** ROWS(D$5:D5)*2-1**

This will return the row number.

**➤**** INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)**

This will return the value from the range **$B$5:$B$14**.

- Select
**Cell E5**. - Type the formula:

`=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)`

- Then hit
**Enter**and use the**Fill Handle**to autofill the below cells.

**Formula Breakdown**

**➤**** ROWS(E$5:E5)*2**

This will return the row number.

**➤**** INDEX($B$5:$B$14,ROWS(E$5:E5)*2)**

This will return the value from the range **$B$5:$B$14**.

**Read More: VBA to Split String into Multiple Columns in Excel (2 Ways)**

### 3. Excel Formula with Combination of LEFT, MID & RIGHT Functions to Split a Text String

Sometimes we need to split a text string. Microsoft Excel **LEFT Function** returns the leftmost characters of a text string and the **RIGHT function** helps us to extract the last characters from a text string. On the other hand, **the MID function** pulls out the middle characters from the middle of a text string. The combination of Excel **LEFT**, **MID** & **RIGHT functions **help us to split one text string into multiple columns. Here we have a dataset (**B4:E9**) of sold items. We are going to split the sold item into three columns (**CODE**, **SERIES**, **NUMBER**).

**STEPS:**

- Select
**Cell C5**. - Next type the formula:

`=LEFT(B5,3)`

- Press
**Enter**and use the**Fill Handle**tool to the below cells.

- Now select
**Cell D5**. - Type the formula:

`=MID(B5,4,1)`

- Hit
**Enter**and use the**Fill Handle**to see the result.

- Again select
**Cell E5**. - Write down the formula:

`=RIGHT(B5,3)`

- Finally, hit
**Enter**and use the**Fill Handle**tool to see the result.

**Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)**

### 4. Excel IF Formula to Split

To run a logical test in a given range, we use Excel **IF function**. It returns the value whether it is **TRUE **or **FALSE**. Let’s say we have a dataset (**B4:F8**) of customer payment history. We are going to split up the column named **AMOUNT** into two columns (**CASH** & **CARD**).

- In the beginning, select
**Cell E5**. - Next type the formula:

`=IF(C5="Cash",D5,"N/A")`

- Now press
**Enter**and use the**Fill Handle**tool to see the result.

This formula will return the **AMOUNT** value which is paid in Cash in **Cell E5**. Otherwise, it will return ‘**N/A**’.

- Then select
**Cell F5**. - After that, type the formula:

`=IF(C5="Card",D5,"N/A")`

- At last, press
**Enter**and use the**Fill Handle**tool to the below cells.

This formula will return the **AMOUNT** value which is paid in Card in **Cell F5**. Otherwise, it will return ‘**N/A**’.

**Read More: How to Split One Cell into Two in Excel (5 Useful Methods)**

### 5. Combination of IFERROR, MID, SEARCH Functions to Split Middle Word

To avoid any error in the formula, we use **the IFERROR function** as it returns with another possible result. Sometimes we have a dataset where each cell contains three words. We can use the **MID function** to extract the middle word. But if there is no middle word, it will show an error. For that, we use the **IFERROR function** along with the **MID **& **SEARCH functions** to Split the middle word in Excel. Suppose we have a dataset (**B4:C9**) containing different writers’ names.

**STEPS:**

- First, select
**Cell D5**. - Next type the formula:

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

- In the end, press
**Enter**and use the**Fill Handle**tool to the below cells.

**Formula Breakdown**

**➤**** SEARCH(” “,B5)**

This will search for the space and return with the position of the space with the **SEARCH function**.

**➤**** MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5))**

This will return the middle word by using the position difference between the first and second space.

**➤**** IFERROR(MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)),””)**

This will return a blank space if there is no middle word in the cell.

### 6. Excel Formula with SUBSTITUTE Function to Split Date

To replace a specific character in a given range with another, we use the Excel **SUBSTITUTE function**. We can use an Excel formula with **SUBSTITUTE**, **LEN** & **FIND functions** wrapped in the **RIGHT function** to split the date from the cell. We have to remember that the formula can be used only when there is a date at the end of the cell like the below dataset (**B4:C8**).

**STEPS:**

- Select
**Cell C5**at first. - Next write down the formula:

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

- Finally, hit
**Enter**and use the Fill**Handle**tool to autofill the cells.

**Formula Breakdown**

**➤**** LEN(B5)**

This will return the length of the text string.

**➤**** SUBSTITUTE(B5,” “,””)**

This will replace all the spaces in **Cell B5**.

**➤**** LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))**

This will subtract the length without space from the total length.

**➤**** SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2)**

This will place ‘**~**’ character between the name and the date.

**➤**** FIND(“~”,SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2))**

This will find the position of ‘**~**’ character which is ‘**4**’.

**➤**** RIGHT(B5,LEN(B5)-FIND(“~”,SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2)))**

This will extract the date from the text string.

**Read More: Excel Formula to Split String by Comma (5 Examples)**

### 7. Excel Formula to Split Text Using CHAR Function

Excel **CHAR function** is a **Text function**. It means **CHARACTER**. It returns a character that is specified by the ASCII code number. We can use the **CHAR function** to split text by line break as this function supplies the break character. Assuming we have a dataset (**B4:C8**) of Microsoft products name with year. We are going to extract the product name using the **CHAR **& **SEARCH functions** wrapped into the **LEFT function**. Here the ASCII code for the line is **10**.

**STEPS:**

- Select
**Cell C5**. - Now type the formula:

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

- Then hit
**Enter**and use**Fill Handle**to see the result.

**Formula Breakdown**

**➤**** SEARCH(CHAR(10),B5,1)-1**

This will search for the position of the text string which is ‘**5**’.

**➤**** LEFT(B5, SEARCH(CHAR(10),B5,1)-1)**

This will return the leftmost value.

**Read More: Excel VBA: Split String by Character (6 Useful Examples)**

### 8. FILTERXML Formula to Split in Excel

To see the output text as a dynamic array after splitting, we can use the Excel **FILTERXML function**. It is available in **Microsoft Excel 365**. Let’s say we have a dataset (**B4:B8**) of customers’ payment history. We are going to split the customer names and the payment methods.

**STEPS:**

- First, select
**Cell C5**. - Next, write down the formula:

`=TRANSPOSE(FILTERXML("<s>"&SUBSTITUTE(B5,",","</s><s>")& "</s>","//s"))`

Here the sub-node is represented as ‘**s**’ and the main-node is represented as ‘**t**’.

- Then press
**Enter**and use**Fill Handle**to autofill the below cells.

**Formula Breakdown**

**➤**** FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”)**

This will turn the text strings into XML strings by changing the delimiter characters into XML tags.

**➤**** TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”))**

The **TRANSPOSE function** will return the output horizontally instead of vertically.

**Read More: How to split a single cell in half in Excel (diagonally & horizontally)**

## Conclusion

These are the quickest way to use Excel Formula to split. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.