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

To illustrate how to split text after a certain word in Excel, we’ll use the following dataset as an example.

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

STEPS:

• Select cell D5. Add the formula:
`=LEFT(C5,FIND(" ",C5)-1)`
• Press Enter.

• 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. It subtracts 1.

• LEFT(C5,FIND(” “,C5)-1)

The LEFT function returns Microsoft.

## Method 2 – Merging Excel RIGHT, FIND, and LEN Functions to Split Text after a Specific Word

STEPS:

• Select cell D5 and add the formula:
`=RIGHT(C5,LEN(C5)-FIND(" ",C5))`
• Press Enter.
• 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

## STEPS:

• Select cell D5 and add 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)

The LEN function returns 18.

• MID(C5,SEARCH(” “,C5),LEN(C5)-(SEARCH(” “,C5)-1))

The MID function returns Outlook.

Read More: Split String by Character in Excel

## Method 4 – Applying LEFT and SEARCH Functions for Dividing Text after a Particular Word

STEPS:

• Select cell D5 and add the formula:
`=LEFT(C5,SEARCH(" ",C5)-1)`
• Press Enter.
• Use AutoFill.

How Does the Formula Work?

• SEARCH(” “,C5)

SEARCH function returns 10.

• LEFT(C5,SEARCH(” “,C5)-1)

LEFT function returns Microsoft.

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

STEPS:

• Select the range C5:C9.
• Select Data ➤ Data Tools ➤ Text to Columns.

• A dialog box will pop out.
• Select the circle for Delimited and click Next.

• Check Space from the Delimiters options.
• Click Next.

• In the Destination box, enter:
`=D5:E9`
• Click Finish.

• It’ll spill the separated texts in the mentioned range.

Read More: How to Separate Two Words in Excel

## Method 6 – Using Power Query Tool for Splitting Text after a Certain Word

STEPS:

• Select Data ➤ Get Data ➤ From File ➤ From Excel Workbook.

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

• Choose the desired sheet and click Transform Data.

• Select the 2nd.
• Select Split Column ➤ By Delimiter.

• Choose Space and click OK.

• It’ll return a new worksheet with the separated texts.

## Method 7 – Inserting Excel FILTERXML Formula to Split Text After Certain Text

STEPS:

• Select cell D5 and add the formula:
`=TRANSPOSE(FILTERXML("<s>"&SUBSTITUTE(C5," ","</s><s>")&"</s>","//s"))`
• 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”)

The formula converts the text strings into XML strings.

• TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(C5,” “,”</s><s>”)&”</s></t>”,”//s”))

TRANSPOSE function will spill the output horizontally.

## Related Articles

<< Go Back to Splitting TextSplit in ExcelLearn 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