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.

## 1. Combining LEFT and FIND Functions to Split Text After a Certain Position in Excel

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

## 2. Merging 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 Split Text in Excel Using Formula

## 3. Separating Text with Excel MID, SEARCH, and LEN Functions in Excel

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

## 4. Applying 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

## 5. Splitting Text with Excel Text to Columns Tool After a Certain Text

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. Using Power Query Tool for Splitting Text after a Certain Word

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

## 7. Inserting Excel FILTERXML Formula to Split Text After Certain 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.

**Download Practice Workbook**

Download the following workbook to practice by yourself.

## 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. Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

- Split Text in Excel by Character
- How to Split First And Last Name in Excel
- How to Split String by Length in Excel

**<< Go Back to Splitting Text |Â Split in Excel |Â Learn Excel**