How to Split Text after a Certain Word in Excel (7 Ways)

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:

• 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:

• 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:

• 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:

• 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:

• 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:

• 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