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.

**Table of Contents**hide

## 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,

**, from the code in cell**

*Apple***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

**from the texts in the adjacent cells.**

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