How to Extract Text after Second Space in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Use MID Function to Extract Text after Second Space in Excel

  • Secondly, select Cell E5 and type the formula:
=MID(D5,FIND(" ",D5,FIND(" ",D5)+1)+1,256)
  • Then, press Enter.

Use MID Function to Extract Text after Second Space in Excel

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

Use MID Function to Extract Text after Second Space in Excel

πŸ”Ž 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.

Extract Text after Second Space with Excel TRIM Function

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

Extract Text after Second Space with Excel TRIM Function

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

Extract Text after Second Space with Excel TRIM Function

πŸ”Ž 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.

Apply RIGHT Function in Excel to Take out Text after Second Space

  • After that, type the formula in Cell E5:
=RIGHT(D5,LEN(D5)-(SEARCH(" ",D5,SEARCH(" ",D5)+1)))
  • Next, hit Enter to see the result.

Apply RIGHT Function in Excel to Take out Text after Second Space

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

Apply RIGHT Function in Excel to Take out Text after Second Space

πŸ”Ž 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.

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

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

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

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

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

πŸ”Ž 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.

Extract Text from the Right in Excel

  • Secondly, type the formula in Cell E5:
=TRIM(LEFT(RIGHT(SUBSTITUTE(D5," ",REPT(" ",100)),200),200))
  • After that, hit Enter to see the result.

Extract Text from the Right in Excel

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

Extract Text from the Right in Excel

πŸ”Ž 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.

Draw out Text after Second Space Using the Excel Power Query

  • Secondly, go to the Insert tab and select Table.

Draw out Text after Second Space Using the Excel Power Query

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

Draw out Text after Second Space Using the Excel Power Query

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

Draw out Text after Second Space Using the Excel Power Query

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

Draw out Text after Second Space Using the Excel Power Query

  • 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


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo