Thea dataset that contains information about the **Sales Amount **and sellers’ performance. You want to extract text from **Column D**.

### Method 1- Using the MID Function to Extract Text after the Second Space in Excel

Use **the MID Function **and **the FIND Function**. **The MID Function** returns the character in the middle of a text string if a starting point and length are provided. **The FIND Function**Â returns the starting position of a text string within another text string.

**STEPS:**

- Create a
**Helper**column.

- Select
**E5**and enter the formula:

`=MID(D5,FIND(" ",D5,FIND(" ",D5)+1)+1,256)`

- Press
**Enter**.

- Drag the
**Fill Handle**down to see the result in the rest of the cells.

**Formula Breakdown**

**FIND(” “, D5, FIND(” “, D5)+1)+1**

The **FIND Function** finds the spaces in **D5** and returns the position of the second space.

**MID(D5, FIND(” “, D5, FIND(” “, D5)+1)+1,256)**

The **MID Function**Â extracts the text string after the second space. The **first argument **is the text string in **D5**. The **second argument** refers to the starting position of the second space and the **third argument** is the number of characters.

**Note: **To extract text after the second comma or any delimiter, you need to replace the space with the comma or the delimiter as shown below:

`=MID(D5,FIND(",",D5,FIND(",",D5)+1)+1,256)`

**Read More: How to Extract Text Before Character in Excel**

### Method 2 – Extracting Text after the Second Space with the TRIM Function

**The TRIM Function** can also extract text after the second space. Use the **TRIM**, **MID**, **FIND, **and **SUBSTITUTE **functions. **The TRIM Function** removes extra spaces from a text string and keeps only single spaces between words. **The SUBSTITUTE Function** replaces existing text with new text.

**STEPS:**

- Create a
**Helper**column.

- Select
**Â E5**and enter the formula:

`=TRIM(MID(D5,FIND("#",SUBSTITUTE(D5," ","#",2))+1,255))`

- Press
**Enter**to see the result.

- Drag the
**Fill Handle**down to see the result in the rest of the cells.

**Formula Breakdown**

**SUBSTITUTE(D5,” “,”#”,2)**

The **SUBSTITUTE Function** finds and replaces the **second space **withÂ **#**Â in **D5**.

**FIND(“#”,SUBSTITUTE(D5,” “,”#”,2))+1**

The **FIND Function** returns the position of **#**.

**TRIM(MID(D5,FIND(“#”,SUBSTITUTE(D5,” “,”#”,2))+1,255))**

The **MID Function****Â **returns the characters in**Â D5 **from the position returned by the **FIND Function** . The **TRIM Function** removes extra spaces keeping the spaces between words.

**NOTE: **If you want to extract text after the **nth **space, you need to replace **2 **with the position of the space. For example, if you want to extract text after the **5th **space, use the formula below:

`=TRIM(MID(D5,FIND("#",SUBSTITUTE(D5," ","#",5))+1,255))`

### Method 3 – Applying the RIGHT Function in Excel to Take out Text after the Second Space

Use **the RIGHT Function **with the **LEN **and **SEARCH **Functions together.** The** **RIGHT Function**** **returns the last character or characters in a text string depending on the specific number of characters. **The LEN Function **calculates the length of a text and **the SEARCH Function **returns the number of the character at which a character or a text is first found reading from the left to the right.

**STEPS:**

- Create a
**Helper**column.

- Enter the formula in
**E5**:

`=RIGHT(D5,LEN(D5)-(SEARCH(" ",D5,SEARCH(" ",D5)+1)))`

- Press
**Enter**to see the result.

- Drag down the
**Fill Handle**.

**Formula Breakdown**

**SEARCH(” “,D5,SEARCH(” “,D5)+1**

The **SEARCH Function** returns the number of characters at which the second space is in **D5**.

**LEN(D5)-SEARCH(” “,D5,SEARCH(” “, D5)+1**

This formula subtracts the number of characters of the position of the second space from the length of the text string in**Â D5**.

**RIGHT(D5, LEN(D5)-(SEARCH(” “, D5, SEARCH(” “, D5)+1)))**

The **RIGHT Function **returns the text after the second space.

**Read More: How to Extract Text after a Specific Text in Excel**

### Method 4 – Combine the INDEX and theÂ FILTERXML Functions to Extract Text after the Second Space

Use the combination of theÂ **INDEX,** **FILTERXML****, **and **SUBSTITUTE **functions.

**STEPS:**

- Create a
**Helper**column.

- Select
**E5**and enter the formula:

`=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(D5," ","</b><b>")&"</b></a>","//b"),3)`

- Press
**Enter**to see the result in**Â E5**.

- Drag the
**Fill Handle**down to see the result in the rest of the cells.

**Formula Breakdown**

**SUBSTITUTE(D5,” “,”</b><b>”)&”</b></a>”,”//b”)**

The **SUBSTITUTE Function****Â **finds and replaces the **second space **with the special characters in**Â D5**.

**FILTERXML(“<a><b>”&SUBSTITUTE(D5,” “,”</b><b>”)&”</b></a>”,”//b”),3)**

This **FILTERXML Function **splits the text string using delimiters and returns the specific value after the second space from the text string in**Â D5**.

**INDEX(FILTERXML(“<a><b>”&SUBSTITUTE(D5,” “,”</b><b>”)&”</b></a>”,”//b”),3)**

The **INDEX Function** returns the third text after the second space.

### Method 5 – Extract Text from the Right in Excel

You we have a text string: â€˜**Performance is Good**â€™. Extract â€˜**is Goodâ€™ **usingÂ the **TRIM**, **LEFT**, **RIGHT**, **SUBSTITUTE**, and **REPT **functions.

**STEPS:**

- Create a
**Helper**column.

- Enter the formula in
**Â E5**:

`=TRIM(LEFT(RIGHT(SUBSTITUTE(D5," ",REPT(" ",100)),200),200))`

- Press
**Enter**to see the result.

- Use the
**Fill Handle**to copy the formula to the rest of the cells.

**Formula Breakdown**

**SUBSTITUTE(D5,” “,REPT(” “,100))**

The **SUBSTITUTE Function****Â **looks for the space in **D5 **and replaces it with repetitive spaces. The **REPT Function **repeats the space **100 **times.

**LEFT(RIGHT(SUBSTITUTE(D5,” “,REPT(” “,100)),200),200)**

This formula returns the specified number of characters after the second space from the right side of the text string in **D5**.

**TRIM(LEFT(RIGHT(SUBSTITUTE(D5,” “,REPT(” “,100)),200),200))**

The **TRIM Function**Â removes extra spaces and returns the text after the second space from the right side.

**Read More: How to Extract Text After First Space in Excel**

### Method 6 – Extracting the Text after the Second Space Using the Excel Power Query

**STEPS:**

- Convert the dataset into a table. Select any cell in the dataset. Here,
**B4**.

- Go to the
**Insert**tab and select**Table**.

- A
**Create Table**message box will pop up. Check the**My table has headers**field if your table contains headers. Otherwise, uncheck it. Click**OK**to proceed.

- The dataset will be converted into a table.

- Go to the
**Data**tab and select**From Table/Range**.

- The
**Power Query**window containing the table is displayed.

- Select the column from which you need to extract text. Here,
**â€˜Commentâ€™**.

- Select
**Transform**and choose**Extract**. - Select
**Text After Delimiter**.

- In the Text After Delimiter dialog box, select
**Delimiter**Â and enter a**single space**.

- Select
**Advanced options**and enter**1**in ‘**Number of delimiters to****skip**‘.

- Click
**OK**to see the result.

- Go to the
**Home**tab in the**Power Query**window and click**Close & Load**.

- You will see texts after the second space in the
**Comment**column in a new sheet.

**Similar Readings**

**How to Extract Text After Last Space in Excel****How to Extract Text Between Two Commas in Excel****How to Extract Text After a Character in Excel**

**Download Practice Book**

Download the practice book here.

## Related Readings

**How to Extract Text after Second Comma in Excel****How to Extract Text between Two Spaces in Excel****How to Extract Text Between Two Characters in Excel****How to Extract Certain Text from a Cell in Excel VBA**

**<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel**