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

__Method 1 –__

** Steps**:

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

- Enter the second product’s name
in cell*Apricots***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*

**Read More: How to Extract Text Before Character in Excel**

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

__Method 2 –__

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

__Method 3 –__

** 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 textfrom the codes.*XYZ*

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

__Method 4 –__

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

__Method 5 –__

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

__Method 6 –__

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

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

__Method 7 –__

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

__Method 8 –__

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

__Method 9 –__

** 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
of the wizard.*Step-2* - 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

__Method 10 –__

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

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

*XYZ*- Press
**F5**.

**Download Workbook**

## Related Readings

**How to Extract Text after Second Comma in Excel****How to Extract Text between Two Spaces in Excel****How to Extract Text Between Two Characters in Excel****How to Extract Certain Text from a Cell in Excel VBA****How to Extract Text After a Character in Excel**

**<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel**