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.

Split Text after a Certain Word in Excel


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.

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

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

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

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

Separate Text with Combination of MID, SEARCH, and LEN Functions

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

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

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

Break Text with Excel ‘Text to Columns’ Feature

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

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

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

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

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

Read More: How to split text into multiple cells in Excel


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.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo