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.
=RIGHT(B4,LEN(B4)-SEARCH("XYZ",B4)-2)
Here, B4 is the product code.
- 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 for completing 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.
- 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.
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.
=MID(B4,FIND("XYZ",B4)+3,LEN(B4)-FIND("XYZ",B4)-2)
Here, B4 is the product code.
- 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, LEFT function, and FIND function.
Steps:
➤ Type the following formula in cell C4.
=SUBSTITUTE(B4,LEFT(B4,FIND("XYZ",B4)+2),"")
Here, B4 is the product code.
- 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.
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.
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4,"XYZ","*",1))-2)
Here, B4 is the product code.
- 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.
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.
=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: 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.
=RIGHT(SUBSTITUTE(B4,"XYZ",REPT("",LEN(B4))),LEN(B4))
Here, B4 is the product code.
- 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 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.