In this article, we will learn to extract text after the second space in Excel. Sometimes, we download or export datasets from different sources and we need the texts after a certain space or comma from that dataset. To extract text after the second space in excel, we can use some **formulas** and also the **Power Query**. Today, we will demonstrate **6 **methods to extract text after the second space in an Excel worksheet.

**Table of Contents**hide

## How to Extract Text after Second Space in Excel: 6 Ways

To explain the methods, we will use a dataset that contains information about the **Sales Amount **and the performance of some sellers. We will try to extract text from **Column D **in this article.

### 1. Use the MID Function to Extract Text after the Second Space in Excel

In the first method, we will use** the MID Function**. Inside **the MID Function**, we will insert **the FIND Function**. **The MID Function** returns the character from 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.

Let’s follow the steps below to learn more.

**STEPS:**

- First of all, create a
**Helper**column like the picture below.

- Secondly, select
**Cell E5**and type the formula:

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

- Then, press
**Enter**.

- Finally, drag the
**Fill Handle**down to see results in the rest of the cells.

🔎 **How Does the Formula Work?**

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

Here, the **FIND Function** finds the spaces in **Cell D5** and then 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 **denotes the text string that **Cell D5 **contains. The **second argument** refers to the starting position of the second space and the **third argument** denotes 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 like the formula below:

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

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

### 2. Extract Text after Second Space with Excel TRIM Function

**The TRIM Function** can also extract text after the second space. Here, we will use the **TRIM**, **MID**, **FIND, **and **SUBSTITUTE **functions together. **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. Here, we will use the previous dataset again.

Let’s pay attention to the steps below to know more.

**STEPS:**

- In the beginning, we need to create a
**Helper**column like the one below.

- After that, select
**Cell E5**and type the formula:

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

- Then, hit
**Enter**to see the result.

- Finally, use the
**Fill Handle**to see results like the picture below.

🔎 **How Does the Formula Work?**

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

Here, the **SUBSTITUTE Function** finds and replaces the **second space **with the **#** character in **Cell D5**.

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

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

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

The **MID Function**** **returns the characters of the text string of **Cell D5 **from the position the **FIND Function** returned. In the end, 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, then, use the formula below:

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

### 3. Apply the RIGHT Function in Excel to Take out Text after the Second Space

To take out text after the second space, we can also 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 left to right.

Let’s observe the steps below to learn more.

**STEPS:**

- In the first place, create a
**Helper**column like the picture below.

- After that, type the formula in
**Cell E5**:

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

- Next, hit
**Enter**to see the result.

- In the end, drag down the
**Fill Handle**to see results like the image below.

🔎 **How Does the Formula Work?**

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

Here, the **SEARCH Function** returns the number of characters at which the second space is in **Cell 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 of **Cell D5**.

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

Finally, the **RIGHT Function **returns the text after the second space.

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

### 4. Combine INDEX and FILTERXML Functions to Pull out Text after Second Space

We can also use the combination of the **INDEX,** **FILTERXML****, **and **SUBSTITUTE **functions together to pull out text after the second space. This method works in *Excel 365* only. Here, we will use the same dataset. So, let’s follow the steps below to know more about this method.

**STEPS:**

- Firstly, we need to create a
**Helper**column like the one below.

- Secondly, select
**Cell E5**and type the formula:

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

- After that, hit
**Enter**to see the result in**Cell E5**.

- Finally, drag the
**Fill Handle**down to see results in the rest of the cells.

🔎 **How Does the Formula Work?**

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

Here, the **SUBSTITUTE Function**** **finds and replaces the **second space **with the special characters in **Cell D5**.

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

This **FILTERXML Function **splits the text string by delimiters and returns the specific value after the second space from the text string of **Cell D5**.

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

Finally, the **INDEX Function** returns the third text after the second space.

### 5. Extract Text from the Right in Excel

In this method, we will extract text after the second space from the right side. For example, if we have a text string, ‘**Performance is Good**’, then, we will extract ‘**is Good’**. To do so, we will use the **TRIM**, **LEFT**, **RIGHT**, **SUBSTITUTE**, and **REPT **functions together.

Let’s follow the steps below.

**STEPS:**

- First of all, create a
**Helper**column like the picture below.

- Secondly, type the formula in
**Cell E5**:

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

- After that, hit
**Enter**to see the result.

- In the end, use the
**Fill Handle**to copy the formula in the rest of the cells.

🔎 **How Does the Formula Work?**

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

Here, the **SUBSTITUTE Function**** **looks for the space in **Cell D5 **and replaces it with repetitive spaces. The **REPT Function **actually 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 **Cell D5**.

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

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

### 6. Draw out Text after the Second Space Using the Excel Power Query

In this last method, we will use the **Power Query **feature of **Excel **to draw out text after the second space or any delimiter. Once again, we will use the same dataset to explain the steps. So, let’s follow the steps below without any delay.

**STEPS:**

- Firstly, convert the dataset into a table. To do so, select any cell in the dataset. We have selected
**Cell B4**here.

- Secondly, 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.

- After that, the dataset will convert into a table like the below image.

- In the following, go to the
**Data**tab and select**From Table/Range**.

- Instantly, the
**Power Query**window will appear containing the table.

- Next, select the column from which you need to extract texts. We have selected the column with the header
**‘Comment’**.

- Now, select
**Transform**and then,**Extract**. A drop-down menu will occur. - Then, select
**Text After Delimiter**from the drop-down menu.

- Again, the Text After Delimiter dialog box will appear.
- Select the
**Delimiter**field and enter a**single space**.

- After that, select
**Advanced options**and type**1**in the ‘**Number of delimiters to****skip**‘ field.

- After entering the values, click
**OK**to see results like the below picture.

- At this moment, go to the
**Home**tab in the**Power Query**window and click the**Close & Load**icon.

- Finally, 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.

## Things to Remember

You can also use the above methods to extract text after the second comma or any delimiter. For that purpose, you just need to replace the space in the formula with the comma or the delimiter.

## Conclusion

We have demonstrated 5 easy methods to Extract Text after the Second Space in Excel. We have used different **formulas **and also the **Power Query **editor. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.