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:

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

Read More: How to Split Text by Space with Formula in Excel

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

STEPS:

• Select cell D5 and add the formula:

• 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

Method 3 – Separating Text with Excel MID, SEARCH, and LEN Functions in Excel

STEPS:

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

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:

• Press Enter.
• Use AutoFill.

How Does the Formula Work?

• SEARCH(” “,C5)

SEARCH function returns 10.

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

LEFT function returns Microsoft.

Read More: How to Split Text by Number of Characters in Excel

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:

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

• Select Close & Load.

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

Read More: How to Split Text in Excel into Multiple Rows

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

STEPS:

• Select cell D5 and add the formula:

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

Download Practice Workbook

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