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.
Split Text in Excel Using Formula: 5 Easy Ways
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.
- 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”
Read More: How to Split Text by Space with Formula in Excel
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.
- 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”
Read More: How to Use Flash Fill in Excel
Similar Readings
- Split a String into an Array in VBA
- How to Split String by Length in Excel
- How to Split Text by Number of Characters 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.
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”
Read More: How to Split First And Last Name in Excel
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
- Follow the breakdown of the first method to understand it. Here, just we replaced the space character with CHAR(10). And 10 is called the ASCII code for the Line Break.
- 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
- 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.
Read More: How to Split Text in Excel by Character
Practice Section
You will get a practice sheet in the Excel file given above to practice the explained ways.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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.
