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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
