How to Extract Text after a Specific Text in Excel?

If you are trying to extract text after a specific text from combined texts in Excel, then you will find this article useful. So, let’s start our main article to learn more about the procedures of doing this task.

How to Extract Text after a Specific Text in Excel: 10 Ways

Here, we have the following dataset containing sales records of different products in different regions. But the company name has been used before the product’s names combining them as codes. By using the following 10 methods we will demonstrate the ways of extracting the name of the products after the specific text XYZ.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

Method-1: Using Flash Fill Feature to Extract Text after a Specific Text

Here, we will use the Flash Fill feature, the simplest and easiest way, for extracting the name of the products after the text XYZ from the codes and then gathering them in the Product column.

Steps:

• First, write down part of the product name, Apple, from the code in cell C4.

• Then, start typing the second product’s name Apricots in cell C5. And, you will see the suggestions showing the names of the products for the rest of the cells due to the Flash Fill feature of Excel.
• Press ENTER.

• Finally, you will get the name of the products extracted from the codes after the text XYZ.

Read More: How to Extract Text Before Character in Excel

Method-2: Using the Combination of the RIGHT, LEN, and SEARCH Functions

In this section, we are going to extract the names of the products from the codes after the specific text XYZ using the combination of the RIGHT function, the LEN function, and the SEARCH function. Let’s get to know the use of these functions to extract text after a specific text in Excel.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• In this way, you will get all of the products’ names in the Product column after the extraction from the codes.

Method-3: Extract Text after a Specific Text Using the RIGHT, LEN, and FIND Functions

Like the previous section here we will also use the same combination of functions except for the SEARCH function, instead of this function we are going to use the FIND function to complete the extraction process.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• Afterward, you will get all of the products’ names in the Product column extracting them after the text XYZ from the codes.

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

Method-4: Using the Combination of MID, LEN, FIND Functions to Extract Text after a Specific Text

In this section, we will be using the MID function, the LEN function, and the FIND function for bringing out the text strings after the specific text XYZ from the codes.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• Afterward, you will get all of the products’ names in the Product column after the extraction from the codes.

Method-5: Extract Text after a Specific Text Using the SUBSTITUTE, LEFT, and FIND Functions

Here, we will extract the names of the products from the codes after XYZ using the combination of the SUBSTITUTE function, the LEFT function, and the FIND function.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• Finally, you will get the name of the products extracted from the codes after the text XYZ.

Method-6: Using the SUBSTITUTE, RIGHT, and FIND Functions

In this section, we will be using the SUBSTITUTE function, the LEN function, the FIND function, and the RIGHT function for bringing out the text strings after the specific text XYZ from the codes.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• Afterward, you will get all of the products’ names in the Product column after the extraction from the codes.

Similar Readings

• How to Extract Text after Second Space in Excel
• How to Extract Text After Last Space in Excel
• How to Extract Text Between Two Commas in Excel

Method-7: Using the REPLACE Function to Extract Text after a Specific Text

Here, we will use the REPLACE function to replace the specific text XYZ with a blank and extract the desired products’ names from the product codes.

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code. REPLACE will supersede the string which starts from position 1 and has a length of 3 with a blank.

• Press ENTER and drag down the Fill Handle tool.

• Eventually, you will get the name of the products extracted from the codes after the text XYZ.

Method-8: Use the RIGHT, SUBSTITUTE, and REPT Functions to Extract Text after a Specific Text

For this section, we will use the combination of the RIGHT function, the SUBSTITUTE function, the REPT function, and the LEN function to extract text after a specific text in Excel

Steps:

• Type the following formula in cell C4.

Here, B4 is the product code.

• Press ENTER and drag down the Fill Handle tool.

• Eventually, you will get all of the products’ names in the Product column after the extraction from the codes.

Method-9: Using the Text to Columns Option

In this method, we will make use of the Text to Columns option for separating the company name XYZ from the products’ names and thus we will have our desired names in the Product column.

Steps:

• Select the range and then go to the Data Tab >> Data Tools Group >> Text to Columns Option.

• After that, the Convert Text to Columns Wizard will appear.
• Click on the Fixed width option and press Next in the first step.

• Then, you will be taken to Step-2 of the wizard.
• Click on the position where you want the separation (as we want to have the division after XYZ so we have clicked after it)
• Press Next.

• Finally, you will reach the final step.
• Select and write the following.
  Column data format → General
  Destination → $B$4
• Press Finish.

• Then, you will have the separated data, and the names of the products will be in the Product column.

Method-10: Using VBA Code to Extract Text after a Specific Text

We will use a VBA code for this section to execute the extraction process and draw out the names of the products after XYZ from the codes.

Steps:

• Go to the Developer Tab >> Visual Basic Option.

• Then, the Visual Basic Editor will open up.
• Go to the Insert Tab >> Module Option.

• After that, a Module will be created.

• Write the following code

Sub gettingtexts()
Dim text As Range
For Each text In Range("B4:B10")
If InStr(text.Value, "XYZ") > 0 Then
text.Offset(0, 1).Value = Right(text.Value, Len(text.Value) _
- InStr(text.Value, "Z"))
Else
text.Offset(0, 1).Value = ""
End If
Next text
End Sub

Here, we have declared text as Range, and it will store each value of the cells for the range B4:B10 within the FOR loop. IF statement will check whether the values contain a specific portion XYZ with the help of the InStr function which looks for a partial match.
For matching the criteria we will extract the portion after XYZ from the texts in the adjacent cells.

• Press F5. In this way, you will get all of the products’ names in the Product column after the extraction from the codes.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover the ways to extract text after a specific text in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

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
• How to Extract Text After a Character in Excel

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