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.

extract text after a specific text in Excel

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.

extract text after a specific text in Excel

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

Flash Fill Feature

➤ 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.

Flash Fill Feature

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

extract text after a specific text in Excel

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.

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

RIGHT,LEN,SEARCH functions

➤ Press ENTER and drag down the Fill Handle tool.

RIGHT,LEN,SEARCH functions

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

RIGHT,LEN,SEARCH functions


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.

extract text after a specific text in Excel

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

RIGHT,LEN,FIND functions

➤ Press ENTER and drag down the Fill Handle tool.

RIGHT,LEN,FIND functions

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

extract text after a specific text 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, LEN function, and FIND function for bringing out the text strings after the specific text XYZ from the codes.

extract text after a specific text in Excel

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

MID,LEN,FIND functions

➤ Press ENTER and drag down the Fill Handle tool.

MID,LEN,FIND functions

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

extract text after a specific text in Excel


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.

extract text after a specific text in Excel

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

SUBSTITUTE,LEFT,FIND Functions

➤ Press ENTER and drag down the Fill Handle tool.

SUBSTITUTE,LEFT,FIND Functions

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

SUBSTITUTE,LEFT,FIND Functions


Similar Readings


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.

extract text after a specific text in Excel

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

SUBSTITUTE,RIGHT,FIND Functions

➤ Press ENTER and drag down the Fill Handle tool.

SUBSTITUTE,RIGHT,FIND Functions

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

extract text after a specific text in Excel

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.

extract text after a specific text in Excel

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.

REPLACE function

➤ Press ENTER and drag down the Fill Handle tool.

REPLACE function

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

REPLACE function


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

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

RIGHT,SUBSTITUTE,REPT Functions

➤ Press ENTER and drag down the Fill Handle tool.

RIGHT,SUBSTITUTE,REPT Functions

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

RIGHT,SUBSTITUTE,REPT Functions


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.

extract text after a specific text in Excel

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

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.

Text to Columns Option

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.

Text to Columns Option

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

Text to Columns Option

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

extract text after a specific text in Excel


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.

extract text after a specific text in Excel

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

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

VBA Code

After that, a Module will be created.

VBA Code

➤ 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.

VBA Code

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

extract text after a specific text in Excel

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.

Practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo