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