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.

**Table of Contents**hide

**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**.

- 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: ****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**.

- 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 Separate Words in Excel Using Formula (Ultimate Guide)**

**Similar Readings**

**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.

**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 (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.

- 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**.

**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 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.