How to Extract Text after a Specific Text in Excel?

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

Steps:

Enter the product name, Apple, from the code in cell C4.

Enter the second product’s name Apricots in cell C5. Flash Fill feature will show suggestions.
Press ENTER.

The result will show the name of the products extracted from the codes after the text XYZ.

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

Steps:

Add the following formula in cell C4.

=RIGHT(B4,LEN(B4)-SEARCH("XYZ",B4)-2)

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.

The result will show all the product names in the Product column.

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

Steps:

Add the following formula in cell C4.

=RIGHT(B4,LEN(B4)-FIND("XYZ",B4)-2)

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.

It will output the product 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

Steps:

Add the following formula in cell C4.

=MID(B4,FIND("XYZ",B4)+3,LEN(B4)-FIND("XYZ",B4)-2)

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.

The result will show the products’ names in the Product column.

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

Steps:

Add the following formula in cell C4.

=SUBSTITUTE(B4,LEFT(B4,FIND("XYZ",B4)+2),"")

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.

The result will show the name of the products.

Method 6 – Using the SUBSTITUTE, RIGHT, and FIND Functions

Steps:

Add the following formula in cell C4.

=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4,"XYZ","*",1))-2)

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.

The result will show the product names in the Product column.

Similar Readings

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

Steps:

Add the following formula in cell C4.

=REPLACE(B4,1,3,"")

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.

The result will show the name of the products.

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

Steps:

Add the following formula in cell C4.

=RIGHT(SUBSTITUTE(B4,"XYZ",REPT("",LEN(B4))),LEN(B4))

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.

The result will show the product names in the Product column.

Method 9 – Using the Text to Columns Option

Steps:

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

The Convert Text to Columns Wizard will appear.
Click on the Fixed width option and press Next.

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

Select and enter the following.
Column data format → General
Destination → $B$4
Press Finish.

The result will show 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

Steps:

Go to the Developer Tab >> Visual Basic Option.

The Visual Basic Editor will open up.
Go to the Insert Tab >> Module Option.

A Module will be created.

Add 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

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.