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.


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

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.

Use LEFT and FIND Functions to Split Text in Excel

  • Finally, drag down the Fill Handle icon to copy the formula for the other cells.

Use LEFT and FIND Functions to Split Text in Excel

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”

Read More: Split String by Character in Excel (6 suitable Ways)


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.

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

  • Then use the Fill Handle tool to copy the formula.

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

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”

Read More: How to Separate Words in Excel Using Formula (Ultimate Guide)


Similar Readings


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.

Insert MID and SEARCH Functions in Excel to Split Text

  • Later, to copy the formula, use the Fill Handle tool.

Insert MID and SEARCH Functions in Excel to Split Text

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 (6 Easy Ways)


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.

Apply Excel FILTERXML Function to Split Text

  • 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”}

Read More: How to Separate Two Words in Excel (6 Easy Ways)


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.

Use of Combined Formula to Split Text with Line Break

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.

Use of Combined Formula to Split Text with Line Break

Formula Breakdown

  • Finally, use the Fill handle tool.

Soon after you will get the output like the image below.

Read More: How to Split Text by Space with Formula in Excel (5 Ways)


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.


Related Articles

Mithun

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

Leave a reply

ExcelDemy
Logo