If you are searching for ways to copy a cell to another sheet in Excel if the cell contains text, then you will find this article useful.

Sometimes you may need to combine the texts and their corresponding values in a different worksheet in a quick way and to serve this purpose we have discussed here different 7 methods regarding this issue.

## Download Workbook

## 7 Ways to Copy to Another Sheet If Cell Contains Text in Excel

Here, we have the following dataset containing the records of product codes, product list, salesperson’s names, and their corresponding sales values of a company. By using this dataset we will demonstrate how to copy the values of the corresponding cells if they contain texts.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Using Filter Option for Any Text Strings

Here, we have some product codes written in number format and some in text format and our task is to combine these text product codes and their corresponding values in a new sheet. For this purpose, we will be using the **Filter** option.

** Steps**:

➤ Select the dataset and then go to the

**Home**Tab >>

**Editing**Group >>

**Sort & Filter**Group >>

**Filter**Option.

In this way, the ** Filter **option will be enabled for this range.

➤ Click on the

**Filter**drop-down symbol of the

**Product Code**column.

➤ Select the **Text Filters **option and then the **Custom Filter **option.

After that, the **Custom AutoFilter **dialog box will open up.

➤ Select the **contains **option in the first box, and then write the symbol *** **in the second box for allowing texts only.

➤ Finally, press **OK**.

Then, you will get the text product codes and their corresponding values.

Now, our task is to copy these values to a new sheet.

➤ Press **CTRL+C **to copy the visible values of the dataset, and then go to the new sheet ** Product_Code**.

➤ Select the cell (**B4**) of the new sheet where you want to paste the values and press **CTRL+V**.

Finally, you will get the product codes in text format and their corresponding values in the new sheet ** Product_Code**.

__Method-2__: If Cell Contains Text Then Copy to Another Sheet Using the ISTEXT Function

Here, we will only combine the text product codes from the **ISTEXT **sheet to the new sheet **Product_Code1 **by using the **IF function** and the **ISTEXT function**.

In the **Product Code **column of the ** Product_Code1 **sheet, we will combine the product codes in text format.

** Steps**:

➤ Type the following formula in cell

**B4**of the sheet

**.**

*Product_Code1*

`=IF(ISTEXT(ISTEXT!B4),ISTEXT!B4,"")`

Here, **ISTEXT!B4 **is the ** Product Code **in cell

**B4**of the

**ISTEXT**sheet and

**ISTEXT**will check if the cell value is text or not, if it is text then

**IF**will return this value otherwise a blank.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the following product codes in text format in the new sheet ** Product_Code1**.

**Read More: If Cell Contains Text Then Add Text in Another Cell in Excel**

__Method-3__: Using the ISNUMBER and SEARCH Functions

In this section, we will check for the specific text ** Cherry **in the

**Product**column in the

**ISNUMBER**sheet,

and then, extract the corresponding values of this text to the new sheet ** Product_Name** by using the

**IF function**,

**ISNUMBER function**,

**SEARCH function**.

** Steps**:

➤ Type the following formula in cell

**B4**of the sheet

**.**

*Product_Name*

`=IF(ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)),ISNUMBER!B4,"")`

Here, **ISNUMBER!B4 **is the ** Product Code **in cell

**B4**of the

**ISNUMBER**sheet.

becomes`SEARCH("Cherry",ISNUMBER!B4)`

returns the number of the character at which the given text string is found first.`SEARCH("Cherry", "Cherry")`

→**Output →**1

becomes`ISNUMBER(SEARCH("Cherry",ISNUMBER!B4))`

returns`ISNUMBER(1)`

→**TRUE**for numbers otherwise**FALSE****Output →**TRUE

becomes`IF(ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)),ISNUMBER!B4,"")`

returns`IF(TRUE,"Cherry","")`

→for*Cherry***TRUE**otherwise blank**Output →**Cherry

➤ Press **ENTER **and drag down the **Fill Handle **tool.

In this way, you will get the text string ** Cherry **and blanks for other products in the

**Product**column.

Similarly, you will get the corresponding salesperson’s names in the **SalesPerson **column by using the following formula

`=IF(ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)),ISNUMBER!C4,"")`

For meeting the criterion it will return the value of cell **C4 **of the **ISNUMBER **sheet.

And for extracting the selling prices use the following formula

`=IF(ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)),ISNUMBER!D4,"")`

For meeting the criterion it will return the value of cell **D4 **of the **ISNUMBER **sheet.

**Read More:** **If Cell Contain Word Then Assign Value in Excel (4 Formulas)**

__Method-4__: If Cell Contains Any Specific Text Then Copy to Another Sheet Using the FILTER Function

Here, we will copy the text string ** Cherry **and its corresponding salesperson’s names, sales Values from the

**sheet**

*Filter*to the new sheet ** Product **by using the

**FILTER**function.

** Steps**:

➤ Type the following formula in cell

**B4**of the sheet

**.**

*Product*

`=FILTER(FILTER!B4:D11,FILTER!B4:B11="Cherry","")`

Here, **FILTER!B4:D11 **is the range of the **FILTER **sheet, then **FILTER **will search for the value ** Cherry **in the range

**FILTER!B4:B11**, and for empty cells, it will return a blank.

After pressing **ENTER**, we will get the salespersons’ names and the sale values for the product ** Cherry**.

__Note:__

*The*

**FILTER function**is only available for Microsoft Excel 365 version.**Similar Readings:**

**How to Return Value If Cells Contain Certain Text from a List****Find text in an Excel range & return cell reference (3 ways)****Excel If Cell Contains Text Then Return Value (8 Easy Ways)**

__Method-5__: Using the INDEX and MATCH Functions

Here, we have the list of the salespersons’ names and selling values of the product ** Cherry **with other products in the

**INDEX-MATCH**sheet.

Using the **SUBTOTAL function**, **INDEX function**, **MATCH function** we will extract those values from the **INDEX-MATCH **sheet, and then combine them in the new sheet **Product1**.

__5.1: Getting the Updated Serial Numbers with the Help of the SUBTOTAL Function__

Firstly, we will get the serial numbers in the **Helper **column which will be automatically updated after filtering.

➤ Type the following formula in cell **B4**

`=SUBTOTAL(3,C$4:C4)`

Here, **3 **is for the **COUNTA** function, **C$4:C4 **is the range which will be updated for each successive row like for **Row 8 **it will be **C$4:C8 **because we have fixed the first limit by putting a **$ **symbol before the **Row number 4**.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the serial numbers in the **Helper **column.

Now, we will enable the filter option for this range.

➤ Select the dataset and then go to the **Home **Tab >> **Editing **Group >> **Sort & Filter **Group >> **Filter **Option.

In this way, you will enable the ** Filter **option for this range.

➤ Click on the

**Filter**drop-down symbol of the

**Product**column.

➤ Check only the product ** Cherry **from the drop-down list of the

**Product**column and press

**OK**.

Then, you will get the filtered table and notice that the serial numbers are automatically updated from **1 **to **4**.

__5.2: Using the Combination of INDEX and MATCH Functions to Extract the Values__

It’s time to copy the values for the product **Cherry **from this sheet to the new sheet ** Product1**.

➤ Go to the new sheet

**and write down the serial numbers in the**

*Product1***Serial No.**column.

➤ Type the following formula in cell **C4**.

`=INDEX('INDEX-MATCH'!$C$4:$C$11,MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0))`

Here, **‘INDEX-MATCH’!$C$4:$C$11 **is the range of the **SalesPerson **column in the **INDEX-MATCH **sheet from which we want to get the corresponding value, **$B4 **is the serial number which will match with the numbers in the range **‘INDEX-MATCH’!$B$4:$B$11 **of the **Helper **column.

returns the row index number of the value in cell`MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0)`

→**$B4**which is**1**.**Output →**1

becomes`INDEX('INDEX-MATCH'!$C$4:$C$11,MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0))`

checks the corresponding value in the range`INDEX('INDEX-MATCH'!$C$4:$C$11,1)`

→**$C$4:$C$11**for row index number**1****Output →**Cherry

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the text strings, **Cherry**, from the old sheet to this new sheet.

Similarly, extract the corresponding salesperson’s names for the product ** Cherry **by using the following formula

`=INDEX('INDEX-MATCH'!$D$4:$D$11,MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0))`

Finally, for the selling prices of the text string **Cherry **apply the following formula

`=INDEX('INDEX-MATCH'!$E$4:$E$11,MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0))`

**Read More: Excel Formula If Cell Contains Text Then Return Value in Another Cell**

__Method-6__: If Cell Contains Any Specific Text Then Copy to Another Sheet Using VLOOKUP

We will copy the text string **Cherry **and its corresponding salespersons’ names, selling prices from the **VLOOKUP **sheet

to the new sheet **Product2** by using the **VLOOKUP function**.

** Steps**:

➤ Follow

**section**

**5.1**to get the following filtered table with updated serial numbers.

To copy these values to the new sheet **Product2** go to this sheet first.

➤ Write down the serial numbers in the **Serial No. **column.

➤ Apply the following formula in cell **C4**.

`=VLOOKUP(B4,VLOOKUP!$B$4:$E$11,2,FALSE)`

**B4 **is the lookup value, **VLOOKUP!$B$4:$E$11 **is the range in the **VLOOKUP **sheet where we will search for the lookup value and extract our desired value, **2 **is the column index number of this range from which we will extract the **Product **names and **FALSE **is for an exact match.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Then, you will get the text strings, **Cherry**, from the old sheet to this new sheet.

Similarly, you will get the corresponding salesperson’s names in the **SalesPerson **column by applying the following formula

`=VLOOKUP(B4,VLOOKUP!$B$4:$E$11,3,FALSE)`

And for having the selling prices use the following formula

`=VLOOKUP(B4,VLOOKUP!$B$4:$E$11,4,FALSE)`

**Read More:** **How to Use VLOOKUP If Cell Contains a Word within Text in Excel**

__Method-7__: Copy to Another Sheet Using a VBA Code in Excel

You can use the **VBA** to check within Excel if a cell contains text and then copy to another sheet. Here, we have our desired product **Cherry **and its corresponding values mixed up with other products in the **VBA **sheet.

Our task is to combine those values in the new sheet **Product3 **using a **VBA **code.

** 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 Copycellstonewsheet()
Dim product As Range
Dim Sht1, Sht2 As Worksheet
Set Sht1 = Worksheets("VBA")
Set Sht2 = Worksheets("Product3")
k = 4
For Each product In Sht1.Range("B4:B11")
If product = "Cherry" Then Sht1.Rows(product.Row).Copy Destination:=Sht2.Rows(k)
k = k + 1
Next product
End Sub
```

Here, we have declared the **product **as **Range** which will be assigned to each cell value of the **Product **column and **Sht1**, **Sht2** as **Worksheet** and we have set **Sht1 **to the old sheet **VBA**, **Sht2 **to the new sheet **Product3**.

The **FOR** loop will search for the text string ** Cherry** in each cell of the

**B4:B11**range of

**Sht1**, the

**IF-THEN**statement will copy the corresponding rows if the cell in the

**Product**column of this row contains

**Cherry**and then paste it to the new sheet.

Here,

**k**is determining the row number where we are pasting the values starting from

**4**and incremented by

**1**.

➤ Press **F5**.

Afterward, you will have the product ** Cherry **and its corresponding

**names,**

*Salesperson’s***in the new sheet**

*Selling Prices***Product3**.

## 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 copy to another sheet if the cell contains 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.