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.
=RIGHT(B4,LEN(B4)-SEARCH("XYZ",B4)-2)
Here, B4 is the product code.
Formula Breakdown
- LEN(B4) becomes
LEN(“XYZApple”) → gives the total number of characters in this text string.
Output → 8
- SEARCH(“XYZ”,B4) becomes
SEARCH(“XYZ”, “XYZApple”) → searches for the text XYZ in XYZApple and gives the position of the first character X in the string.
Output → 1
- LEN(B4)-SEARCH(“XYZ”,B4) becomes
8-1 → 7
- LEN(B4)-SEARCH(“XYZ”, B4)-2 becomes
7-2 → subtracts 2 from the number of texts 7 because of omitting the remainder YZ of XYZ also.
Output → 5
- RIGHT(B4,LEN(B4)-SEARCH(“XYZ”,B4)-2) becomes
RIGHT(“XYZApple”,5) → extracts 5 characters from the right side.
Output → Apple
- 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.
=RIGHT(B4,LEN(B4)-FIND("XYZ",B4)-2)
Here, B4 is the product code.
Formula Breakdown
- LEN(B4) becomes
LEN(“XYZApple”) → gives the total number of characters in this text string.
Output → 8
- FIND(“XYZ”,B4) becomes
FIND(“XYZ”, “XYZApple”) → searches for the text XYZ in XYZApple and gives the position of the first character X in the string.
Output → 1
- LEN(B4)-FIND(“XYZ”,B4) becomes
8-1 → 7
- LEN(B4)-FIND(“XYZ”, B4)-2 becomes
7-2 → subtracts 2 from the number of texts 7 because of omitting the remainder YZ of XYZ also.
Output → 5
- RIGHT(B4,LEN(B4)-FIND(“XYZ”,B4)-2) becomes
RIGHT(“XYZApple”,5) → extracts 5 characters from the right side.
Output → Apple
- 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.
=MID(B4,FIND("XYZ",B4)+3,LEN(B4)-FIND("XYZ",B4)-2)
Here, B4 is the product code.
Formula Breakdown
- FIND(“XYZ”,B4) becomes
FIND(“XYZ”, “XYZApple”) → searches for the text XYZ in XYZApple and gives the position of the first character X in the string.
Output → 1
- FIND(“XYZ”,B4)+3 becomes
1+3 → 3 is added to get the starting position of the text that we want to draw out after XYZ.
Output → 4
- LEN(B4) becomes
LEN(“XYZApple”) → gives the total number of characters in this text string.
Output → 8
- LEN(B4)-FIND(“XYZ”,B4) becomes
8-1 → 7
- LEN(B4)-FIND(“XYZ”, B4)-2 becomes
7-2 → subtracts 2 from the number of texts 7 because of omitting the remainder YZ of XYZ also.
Output → 5
- MID(B4,FIND(“XYZ”,B4)+3,LEN(B4)-FIND(“XYZ”,B4)-2) becomes
MID(“XYZApple”,4,5) → extracts the part starting from position 4 and the total length of the characters would be 5.
Output → Apple
- 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.
=SUBSTITUTE(B4,LEFT(B4,FIND("XYZ",B4)+2),"")
Here, B4 is the product code.
Formula Breakdown
- FIND(“XYZ”,B4) becomes
FIND(“XYZ”, “XYZApple”) → searches for the text XYZ in XYZApple and gives the position of the first character X in the string.
Output → 1
- FIND(“XYZ”,B4)+2 becomes
1+2 → 2 is added to get the total number of characters in text XYZ.
Output → 3
- LEFT(B4,FIND(“XYZ”,B4)+2) becomes
LEFT(“XYZApple”,3) → draws out the first 3 characters from the left of this string.
Output → “XYZ”
- SUBSTITUTE(B4,LEFT(B4,FIND(“XYZ”,B4)+2),””) becomes
SUBSTITUTE(“XYZApple”, “XYZ”,””) → supersedes the part XYZ with a blank in the text XYZApple.
Output → Apple
- 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.
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4,"XYZ","*",1))-2)
Here, B4 is the product code.
Formula Breakdown
- LEN(B4) becomes
LEN(“XYZApple”) → gives the total number of characters in this text string.
Output → 8
- SUBSTITUTE(B4,”XYZ”,”*”,1) becomes
SUBSTITUTE(“XYZApple”, “XYZ”,”*”,1) → replaces the string XYZ with the * symbol and 1 is for the first occurrence.
Output → *Apple
- FIND(“*”,SUBSTITUTE(B4,”XYZ”,”*”,1)) becomes
FIND(“*”,*Apple) → finds the position of the * symbol in the string
Output → 1
- LEN(B4)-FIND(“*”,SUBSTITUTE(B4,”XYZ”,”*”,1)) becomes
8-1 → 7
- FIND(“*”,SUBSTITUTE(B4,”XYZ”,”*”,1))-2 becomes
7-2 → subtracts 2 from the number of texts 7 because of omitting the remainder YZ of XYZ also.
Output → 5
- RIGHT(B4,LEN(B4)-FIND(“*”,SUBSTITUTE(B4,”XYZ”,”*”,1))-2) becomes
RIGHT(“XYZApple”,5) → extracts 5 characters from the right side.
Output → Apple
- 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.
=REPLACE(B4,1,3,"")
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.
=RIGHT(SUBSTITUTE(B4,"XYZ",REPT("",LEN(B4))),LEN(B4))
Here, B4 is the product code.
Formula Breakdown
- LEN(B4) becomes
LEN(“XYZApple”) → gives the total number of characters in this text string.
Output → 8
- REPT(“”,LEN(B4))) becomes
REPT(“”,8) → returns blank
Output → “”
- SUBSTITUTE(B4,”XYZ”,REPT(“”,LEN(B4))) becomes
SUBSTITUTE(“XYZApple”, “XYZ”,””) → replaces the part XYZ with a blank in the text XYZApple.
Output → “Apple”
- RIGHT(SUBSTITUTE(B4,”XYZ”,REPT(“”,LEN(B4))),LEN(B4)) becomes
RIGHT(“Apple”,8) → returns characters up to 8 lengths from the right as there is 5 character it will return 5 characters.
Output → Apple
- 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.