If Cell Contains Text Then Copy to Another Sheet in Excel

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.

Excel if cell contains text then copy to another sheet

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.

Excel if cell contains text then copy to another sheet

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

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.

filter option

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

Excel if cell contains text then copy to another sheet

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.

filter option

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

Excel if cell contains text then copy to another sheet

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.

filter option

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

filter option

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

Excel if cell contains text then copy to another sheet


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.

Excel if cell contains text then copy to another sheet

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

ISTEXT function

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.

Excel if cell contains text then copy to another sheet

➤ Press ENTER and drag down the Fill Handle tool.

ISTEXT function

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

ISTEXT function

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,

Excel if cell contains text then copy to another 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.

Excel if cell contains text then copy to another sheet

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.

  • SEARCH("Cherry",ISNUMBER!B4) becomes
    SEARCH("Cherry", "Cherry")returns the number of the character at which the given text string is found first.
    Output → 1
  • ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)) becomes
    ISNUMBER(1)returns TRUE for numbers otherwise FALSE
    Output → TRUE
  • IF(ISNUMBER(SEARCH("Cherry",ISNUMBER!B4)),ISNUMBER!B4,"") becomes
    IF(TRUE,"Cherry","")returns Cherry for TRUE otherwise blank
    Output → Cherry

ISNUMBER & SEARCH function

➤ Press ENTER and drag down the Fill Handle tool.

ISNUMBER & SEARCH function

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

Excel if cell contains text then copy to another sheet

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.

ISNUMBER & SEARCH function

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.

ISNUMBER & SEARCH function

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 Filter sheet

Excel if cell contains text then copy to another sheet

to the new sheet Product by using the FILTER function.

Excel if cell contains text then copy to another sheet

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.

FILTER function

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

FILTER function

Note:
The FILTER function is only available for Microsoft Excel 365 version.


Similar Readings:


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.

Excel if cell contains text then copy to another 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.

Excel if cell contains text then copy to another sheet


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.

INDEX-MATCH Functions

➤ Press ENTER and drag down the Fill Handle tool.

INDEX-MATCH Functions

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

Excel if cell contains text then copy to another sheet

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.

INDEX-MATCH Functions

In this way, you will enable the Filter option for this range.
➤ Click on the Filter drop-down symbol of the Product column.

INDEX-MATCH Functions

➤ Check only the product Cherry from the drop-down list of the Product column and press OK.

INDEX-MATCH Functions

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

Excel if cell contains text then copy to another sheet


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 Product1 and write down the serial numbers in the Serial No. column.

INDEX-MATCH Functions

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

  • MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0)returns the row index number of the value in cell $B4 which is 1.
    Output → 1
  • INDEX('INDEX-MATCH'!$C$4:$C$11,MATCH($B4,'INDEX-MATCH'!$B$4:$B$11,0)) becomes
    INDEX('INDEX-MATCH'!$C$4:$C$11,1)checks the corresponding value in the range $C$4:$C$11 for row index number 1
    Output → Cherry

INDEX-MATCH Functions

➤ Press ENTER and drag down the Fill Handle tool.

INDEX-MATCH Functions

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

INDEX-MATCH Functions

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

INDEX-MATCH Functions

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

Excel if cell contains text then copy to another sheet

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

Excel if cell contains text then copy to another sheet

to the new sheet Product2 by using the VLOOKUP function.

Excel if cell contains text then copy to another sheet

Steps:
➤ Follow section 5.1 to get the following filtered table with updated serial numbers.

VLOOKUP Function

To copy these values to the new sheet Product2 go to this sheet first.
➤ Write down the serial numbers in the Serial No. column.

VLOOKUP Function

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

VLOOKUP Function

➤ Press ENTER and drag down the Fill Handle tool.

VLOOKUP Function

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

VLOOKUP Function

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)

VLOOKUP Function

And for having the selling prices use the following formula

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

Excel if cell contains text then copy to another sheet

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.

Excel if cell contains text then copy to another sheet

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

Excel if cell contains text then copy to another sheet

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.

Excel if cell contains text then copy to another sheet

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

VBA Code

➤ Press F5.
Afterward, you will have the product Cherry and its corresponding Salesperson’s names, Selling Prices in the new sheet Product3.

VBA Code


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


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