# How to Split Text in Excel Using a Formula (5 Easy Ways)

To showcase the methods, we will use a simple dataset listing some items of clothing and their corresponding colors, and apply a formula to split each item into its Name and Color.

### Method 1- Use LEFT and FIND Functions to Split Text in Excel

This method will be used to split the Name from the text.

The SEARCH function can be used interchangeably with the FIND function.

Steps:

• Enter the following formula in Cell C5
`=LEFT(B5,FIND(" ",B5)-1)`
• Press Enter to get the result.

• Drag down the Fill Handle icon to copy the formula to the other cells in the series.

Formula Breakdown

• FIND(” “,B5)-1
The FIND function returns the position of Â the text specified in the first parameter (â€œ â€œ) within the text specified in the second parameter (cell B5). We subtract 1 from the result, to skip the searched text (â€œ â€œ). The function returns 3.
• LEFT(B5,FIND(” “,B5)-1)
The LEFT function returns only the first x characters of the first parameter (cell B5), where x is the number specified in the second parameter (3), ie our formula seeks the first 3 characters of cell B5, and Â returns “Hat”

### Method 2 – Use RIGHT, LEN, and FIND Functions to Split Text in Excel

This method will be used to split the Color from the text.

The SEARCH function can also be used interchangeably with the FIND function here.

Steps:

• Enter the following formula in Cell C5
`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`
• Press Enter to get the result.

• As in Method 1, use the Fill Handle tool to copy the formula to the rest of the series.

The output should look as follows:

Formula Breakdown

• FIND(” “,B5)
The FIND function (described in Method 1 above) returns 4.
• LEN(B5)-FIND(” “,B5)
The LEN function returns the total Â number of characters in the specified text (Cell B5). We subtract the output of the FIND function from the total length to return the number of characters after the â€œ â€œ, ie 5.
• RIGHT(B5,LEN(B5)-FIND(” “,B5))
The RIGHT function returns only the string in the first parameter (cell B5) remaining after removing the first x characters, where x is the number specified in the second parameter (4), ie our formula seeks the string to the right of the first 4 characters of cell B5. The formulaÂ  returns
“Black”

Read More: How to Split String by Length in Excel

### Method 3 – Insert MID and SEARCH Functions in Excel to Split Text

This method can be used to split text from any position in the middle of a string.

To demonstrate, our dataset has been modified to add a size after the color, so the color is now in the middle position. Our function will return just the color.

Steps:

• In Cell C5, enter the following formula-
`=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1)`
• Press Enter to get the result.

• As with previous Methods, copy the formula using the Fill HandleÂ tool.

The output should look like this:

Formula Breakdown

• SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1
Returns the number of characters to retain, namely
5
• SEARCH(” “,B5)+1
Returns the starting position for the MID function, ie the position of the first space character in cell B5, plus 1 character to skip past it. The function returns
5
• MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1)
The MID function retains the string starting from the position specified in parameter 1, and ending after the number of characters specified in parameter 2. The formula returns
“Black”

### Method 4 – Apply Excel FILTERXML Function to Split Text

Using the FILTERXML function, we can easily split both the name and color at the same time. This method also makes use of the TRANSPOSE and SUBSTITUTE functions.

Steps:

• Enter the following formula in Cell C5:
`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")& "</s></t>","//s"))`
• Use the Fill Handle tool to copy the formula to the other cells in the series.

• Use the Fill Handle tool to copy the formula to the other cells in the series.

Result: all the items and colors have been split into their own columns.

Formula Breakdown

• FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”)
This function returns the text strings as XML strings, by converting the delimiter letters into XML tags. It returns –
{“Hat”,”Black”}
• TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”))
The TRANSPOSE function transposes the output from vertically to horizontally. It returns –
{“Hat”,”Black”}

### Method 5 – Use a Combined Formula to Split Text with Line Breaks.

Text with line breaks can also be split easily using a formula. To demonstrate this method, line breaks have been added to our dataset.

Steps:

To split the item name:

• Enter Â the following formula in Cell C5
`=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)`
• Press Enter to get the result.

Formula Breakdown

• Refer to Method 1 to understand how the formula works. Here, we simply replaced the space character with CHAR(10), which is the ASCII code for the Line Break.

To split the color:

• Enter Â the following formula in Cell D5
`=RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5)+1)`
• Press Enter to get the result.

Formula Breakdown

• Refer to Method 2 above to understand how the formula Â works. We simply replaced the space character with CHAR(10), which is ASCII code for the Line Break.

• As with the other methods, use the Fill handle tool to copy the formula to the other cells in the series.

The output should look as follows:

Read More: How to Split Text in Excel by Character

## Practice Section

Use the Practice Workbook below to practice the different Methods.

## Related Articles

<< Go Back to Splitting Text |Â Split in Excel |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF