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

Excel has many features to split text. But if you want to do it only using formulas then are several ways to do it. And using the formula is one of the quickest ways. Hope this article will show you how to split text in excel using a formula with sharp steps and clear illustrations.

## 5 Ways to Split Text in Excel Using Formula

Let’s get introduced to our dataset first that represents some cloth items and their corresponding colors. We’ll apply a formula to split the cloth Name and Color. ### 1. Use LEFT and FIND Functions to Split Text in Excel

First, we’ll use the LEFT and FIND functions to split the clothes’ names from the text. You can use the SEARCH function instead of the FIND function.

Steps:

• Activate Cell C5.
• Then type the following formula in it-
`=LEFT(B5,FIND(" ",B5)-1)`
• Then just hit the Enter button to get the result. • Finally, drag down the Fill Handle icon to copy the formula for the other cells. Soon after, you will get all the split cloth Names. Formula Breakdown

• FIND(” “,B5)-1
The FIND function will search the position of the space character from the text. Then we subtracted 1 to avoid the position of space. So it will return as-
3
• LEFT(B5,FIND(” “,B5)-1)
Finally, the LEFT function will keep that amount of text from the string. It will return as-
“Hat”

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

Now we’ll split the color from the text using a formula with the RIGHT, LEN, and FIND functions. Here also you can use the SEARCH function instead of the FIND function.

Steps:

• Type the following formula in Cell C5
`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`
• Later, press the Enter button to get the output. • Then use the Fill Handle tool to copy the formula. Then you will get the output like the image below. Formula Breakdown

• FIND(” “,B5)
The FIND function will find the position number of space from the text of Cell B5 and will return as-
4
• LEN(B5)-FIND(” “,B5)
Then the LEN function will find the text length of Cell B5, Then subtracted the output of the FIND function to keep the last portion of the text. So it will return as-
5
• RIGHT(B5,LEN(B5)-FIND(” “,B5))
Finally, the RIGHT function will keep the text from the right side according to the previous output and will return as-
“Black”

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

Now I’ll show how to split text from any middle position of a text using the MID and SEARCH functions. For that, I have modified the dataset a little bit- and added the size after color. Now the color is in the middle position. We’ll split it.

Steps:

• In Cell C5, write the following formula-
`=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1)`
• Then hit the Enter button to finish. • Later, to copy the formula, use the Fill Handle tool. Here’s the output- Formula Breakdown

• SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1
It will find the number of characters that we will have to keep. It returns as-
5
• SEARCH(” “,B5)+1
This past will find the start position for the MID function. So it will return as-
5
• MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1)
Finally, the MID function will keep the text according to the start number and character numbers and it will return as-
“Black”

### 4. Apply Excel FILTERXML Function to Split Text

Using the FILTERXML function, we can easily split the cloth name and color at a time. Also, we’ll have to use the TRANSPOSE and SUBSTITUTE functions with it.

Steps:

• Type the following formula in Cell C5
`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")& "</s></t>","//s"))`
• Press the Enter button and you will get the cloth name and color at a time. • After that, use the Fill Handle tool. Now have a look, all the items and colors are splitted. Formula Breakdown

• FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”)
It will return the text strings into XML strings by converting the delimiter letters into XML tags. That will return as-
{“Hat”,”Black”}
• TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”))
Finally, the TRANSPOSE function will evolve the output horizontally from vertically. So it will return as-
{“Hat”,”Black”}

### 5. Use of Combined Formula to Split Text with Line Break

If the text has a line break then also we can split it easily using a formula. In this dataset, I used line breaks to show cloth names and colors. Let’s split them.

Steps:

• To split the item, type the following formula in Cell C5
`=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)`
• Then hit the Enter button. Formula Breakdown

• Next, to split the color, type the following formula in Cell D5
`=RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5)+1)`
• Press the Enter button. Formula Breakdown

• Finally, use the Fill handle tool. Soon after you will get the output like the image below. ## Practice Section

You will get a practice sheet in the Excel file given above to practice the explained ways. ## Conclusion

I hope the procedures described above will be good enough to split text in Excel using a formula. Feel free to ask any question in the comment section and please give me feedback. #### Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts 