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

**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:

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

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

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

**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:

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

- 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:

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

**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**