Sometimes, we have to **split a sentence or a group of words**. It helps us to understand the data better whenever there is a large text in a single cell. In this article, we’ll show you the simple yet effective methods to **Split Text after **a **Certain Word **in **Excel**.

To illustrate, we’ll use the following dataset as an example. For instance, we’ll split the **Products**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 7 Ways to Split Text after a Certain Word in Excel

### 1. Separate Text after a Certain Word in Excel with LEFT and FIND Functions

We can retrieve the stated number of characters from the beginning of a text string with **the LEFT function**. And **the FIND function** spills the starting position of a text which is in another text string. We’ll use these functions to **Split Text after **a **Certain Word **in **Excel**. Therefore, follow the steps to perform the task.

**STEPS:**

- First, select cell
**D5**. Here, type the formula:

`=LEFT(C5,FIND(" ",C5)-1)`

- Then, press
**Enter**.

- Finally, use
**AutoFill**and it’ll return the desired words.

🔎 **How Does the Formula Work?**

**FIND(” “,C5)-1**

The **FIND **function searches for a **Space **in **C5 **and returns **10 **as the space is found at the **10th **character. Eventually, it subtracts** 1**.

**LEFT(C5,FIND(” “,C5)-1)**

The **LEFT **function returns **Microsoft**.

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

### 2. Combine Excel RIGHT, FIND, and LEN Functions to Split Text after a Specific Word

Moreover, we can retrieve a particular number of characters from the end of a text string with **the RIGHT function**, whereas **the LEN function** spills the number of characters in a text string. So, learn the steps below to **Split Text after **a **Specific Word**.

**STEPS:**

- Firstly, select cell
**D5**to type the formula:

`=RIGHT(C5,LEN(C5)-FIND(" ",C5))`

- Next, press
**Enter**. - Lastly, use
**AutoFill**to complete the rest.

🔎 **How Does the Formula Work?**

**FIND(” “,C5)**

The **FIND **function returns **10**.

**LEN(C5)**

The **LEN **function returns **18**.

**RIGHT(C5,LEN(C5)-FIND(” “,C5))**

The **RIGHT **function returns **Outlook**.

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

### 3. Separate Text with Combination of MID, SEARCH, and LEN Functions

**The MID function** can retrieve the characters from the middle of a text if we provide the starting position and length. **The SEARCH function** spills the number of the character when it’s first found in a text string. Hence, follow the process to **separate text** with a combination of the **MID**, **SEARCH**, and **LEN **functions.

**STEPS:**

- In the beginning, select
**D5**and type the formula:

`=MID(C5,SEARCH(" ",C5),LEN(C5)-(SEARCH(" ",C5)-1))`

- Press
**Enter**and use**AutoFill**to fill the series.

🔎 **How Does the Formula Work?**

**SEARCH(” “,C5)**

The **SEARCH **function returns **10**.

**LEN(C5)**

Then, the **LEN **function returns **18**.

**MID(C5,SEARCH(” “,C5),LEN(C5)-(SEARCH(” “,C5)-1))**

At last, the **MID **function returns **Outlook**.

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

### 4. Apply LEFT and SEARCH Functions for Dividing Text after a Particular Word

Additionally, we can apply the **LEFT **and **SEARCH **functions to divide text strings. Therefore, learn the process to carry out the operation.

**STEPS:**

- First of all, in cell
**D5**, type the formula:

`=LEFT(C5,SEARCH(" ",C5)-1)`

- After that, press
**Enter**. - Lastly, use
**AutoFill**.

🔎 **How Does the Formula Work?**

**SEARCH(” “,C5)**

Firstly, the **SEARCH **function returns **10**.

**LEFT(C5,SEARCH(” “,C5)-1)**

At last, the **LEFT **function returns **Microsoft**.

**Read More: ****How to Split Text by Number of Characters in Excel (7 Ways)**

### 5. Break Text with Excel ‘Text to Columns’ Feature

We can also use the ‘**Text to Columns**’ feature to break texts in Excel. See the below procedure to perform the task.

**STEPS:**

- Select the range
**C5:C9**at first. - Then, select
**Data ➤ Data Tools ➤ Text to Columns**.

- As a result, a dialog box will pop out.
- There, check the circle for
**Delimited**and press**Next**.

- Subsequently, choose
**Space**from the**Delimiters**options. - Press
**Next**.

- Afterward, in the
**Destination**box, type:

`=D5:E9`

- Press
**Finish**.

- In the end, it’ll spill the separated texts in the mentioned range.

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

### 6. Power Query Editor for Splitting Text after a Certain Word in Excel

Another effective way is to use the **Power Query Editor**. Now, follow the below process carefully to **Split Text after **a **Certain Word **in **Excel**.

**STEPS:**

- Firstly, select
**Data ➤ Get Data ➤ From File ➤ From Excel Workbook**.

- Consequently, the
**Import Data**dialog box will appear. - Select the desired file and press
**Import**.

- Now, choose the desired sheet and press
**Transform Data**.

- Next, select the
**2nd.** - Then, select
**Split Column ➤ By Delimiter**.

- After that, choose
**Space**and press**OK**.

- Subsequently, select
**Close & Load**.

- Eventually, it’ll return a new worksheet with the separated texts as it’s shown in the following picture.

**Read More: ****How to Split Text in Excel into Multiple Rows (6 Quick Tricks)**

### 7. Use Excel FILTERXML Formula to Divide Text

In our last method, we’ll use **the FILTERXML formula** to divide texts. Follow along to know how to do the task.

**STEPS:**

- Select cell
**D5**at first. Type the formula:

`=TRANSPOSE(FILTERXML("<s>"&SUBSTITUTE(C5," ","</s><s>")&"</s>","//s"))`

- Next, press
**Enter**. Use**AutoFill**to fill the rest.

**Note:** The sub-node is denoted by ‘**s**’ and the main node is denoted by ‘**t**’.

🔎 **How Does the Formula Work?**

**FILTERXML(“<t><s>”&SUBSTITUTE(C5,” “,”</s><s>”)&”</s></t>”,”//s”)**

Firstly, this formula converts the text strings into **XML **strings.

**TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(C5,” “,”</s><s>”)&”</s></t>”,”//s”))**

At last, **the** **TRANSPOSE function** will spill the output horizontally.

## Conclusion

Henceforth, you will be able to **Split Text after **a **Certain Word **in **Excel **following the above-described methods. Keep using them and let us know if you have any more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.