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.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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
- Activate Cell C5.
- Then type the following formula in it-
- 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.
- 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-
- LEFT(B5,FIND(” “,B5)-1)
Finally, the LEFT function will keep that amount of text from the string. It will return as-
2. Use RIGHT, LEN, and FIND Functions to Split Text in Excel
- Type the following formula in Cell C5–
- 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.
- FIND(” “,B5)
The FIND function will find the position number of space from the text of Cell B5 and will return as-
- 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-
- 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-
- Split a String into an Array in VBA (3 Ways)
- How to Split String by Length in Excel (8 Ways)
- Splitting Text in Excel Using Flash Fill
- How to split text into multiple cells in Excel
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.
- 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-
- SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1
It will find the number of characters that we will have to keep. It returns as-
- SEARCH(” “,B5)+1
This past will find the start position for the MID function. So it will return as-
- 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-
4. Apply Excel FILTERXML Function to Split Text
- Type the following formula in Cell C5–
- 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.
- 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-
- 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-
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.
- To split the item, type the following formula in Cell C5–
- Then hit the Enter button.
- Next, to split the color, type the following formula in Cell D5–
- Press the Enter button.
- Follow the breakdown of the second method to understand it. Here, we just replaced the space character with CHAR(10). And 10 is called the ASCII code for the Line Break.
- Finally, use the Fill handle tool.
Soon after you will get the output like the image below.
You will get a practice sheet in the Excel file given above to practice the explained ways.
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.