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:
`=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.

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.

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.

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.

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

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

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF