How to Extract Text after a Specific Text in Excel (10 Ways)

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 know more about the procedures of doing this task.

Download Workbook

10 Ways to Extract Text after a Specific Text in Excel

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 Microsoft Excel 365 version here, you can use any other versions 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 gather 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, then 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 from a Cell in Excel (5 ways)

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

In this section, we are going to extract the name of the products from the codes after the specific text XYZ using the combination of the RIGHT function, LEN function, and 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 for completing 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.

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, LEN function, and 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, LEFT function, and 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.

Similar Readings

• How to Extract Text after Second Space in Excel (6 Methods)
• Extract Text Before Character in Excel (4 Quick Ways)
• How to Extract Text After Last Space in Excel (5 Ways)
• Extract Text Between Two Characters in Excel (4 Methods)

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

In this section, we will be using the SUBSTITUTE function, LEN function, FIND function, and 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.

Read More: Extract Text After a Character in Excel (6 Ways)

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 so 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: Using 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, SUBSTITUTE function, REPT function, and 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 followings.
Column data format → General
Destination → $B$4
➤ Press Finish.

Then, you will have the separated data and so 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 name 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.

Read More: How to Extract Certain Text from a Cell in Excel VBA (5 Examples)

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.

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 Articles

• How to Extract Text after Second Comma in Excel (6 Methods)
• Extract Text After First Space in Excel (3 Methods)
• How to Extract Text between Two Spaces in Excel (5 Methods)
• Extract Text Between Two Commas in Excel (4 Easy Approaches)