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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

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

## Related Articles Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with 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 Advanced Excel Exercises with Solutions PDF  