We can split a cell, a text string, or a column using an Excel formula. 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 data or strings.

**Table of Contents**hide

## How to Split Data with Formula in Excel: 8 Easy Ways

### 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. Let’s say we have a dataset of range **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. - Then, type the formula.

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

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

- Select cell
**D5**and type the formula below.

`=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)**The

**SEARCH**function returns the position of the space.

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

### 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 of range **B4:B14**. We are going to use the **INDEX-ROWS** formula to split this column into two columns (**Column1 **& **Column2**).

**STEPS:**

- Write down the formula on cell
**D5**.

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

- Type the formula on cell
**E5**.

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

- 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: ****How to Separate Text and Numbers in Excel**

### 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 helps us to split one text string into multiple columns. Here we have a dataset of sold items in range **B4:E9**. We are going to split the sold item into three columns (**CODE**, **SERIES**, **NUMBER**).

**STEPS:**

- Move to cell
**C5**. - Next type the formula below.

`=LEFT(B5,3)`

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

- Insert the formula on cell
**D5**.

`=MID(B5,4,1)`

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

- Again select cell
**E5**and input the fomrula.

`=RIGHT(B5,3)`

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

### 4. Excel IF Formula to Split

To run a logical test in a given range, we will use **the IF function**. It returns the value whether it is **TRUE **or **FALSE**. Let’s say we have a dataset of customer payment history in the range **B4:F8**. 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")`

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

- Now, select cell
**F5**and type the formula below.

`=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 Data in Excel**

### 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 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 containing different writers’ names.

**STEPS:**

- First, select cell
**D5**and insert the formula below.

`=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 return to 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 spaces.

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

- Write down the formula on cell
**C5**.

`=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.

### 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 product names 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:**

- Type the formula on cell
**C5**.

`=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 denoted by

**CHAR(10)**.

**➤**** LEFT(B5, SEARCH(CHAR(10),B5,1)-1)**This will return the leftmost value.

**Read More: ****Split Names in Excel**

### 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 Office 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:**

- Select
**Cell C5**. - Next, write down the formula.

`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")& "</s></t>","//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.

**Practice Workbook**

Download the following workbook and exercise.

## Conclusion

These are the quickest ways 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.