Popular Posts Show
Recent Posts
x 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 StringsHere, 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: In this way, the Filter option will be enabled for this range. ➤ Select the Text Filters option and then the Custom Filter option. After that, the Custom AutoFilter dialog box will open up. Then, you will get the text product codes and their corresponding values. Now, our task is to copy these values to a new sheet. ➤ 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 FunctionHere, 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:
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 FunctionsIn 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:
Here, ISNUMBER!B4 is the Product Code in cell B4 of the ISNUMBER sheet.
➤ 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
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
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 FunctionHere, we will copy the text string Cherry and its corresponding salesperson’s names, sales Values from the Filter sheet to the new sheet Product by using the FILTER function. Steps:
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: Similar Readings:
Method-5: Using the INDEX and MATCH FunctionsHere, 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 FunctionFirstly, we will get the serial numbers in the Helper column which will be automatically updated after filtering.
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. In this way, you will enable the Filter option for this range. ➤ 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 ValuesIt’s time to copy the values for the product Cherry from this sheet to the new sheet Product1. ➤ Type the following formula in cell C4.
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.
➤ 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
Finally, for the selling prices of the text string Cherry apply the following formula
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 VLOOKUPWe 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: To copy these values to the new sheet Product2 go to this sheet first. ➤ Apply the following formula in cell C4.
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
And for having the selling prices use the following formula
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 ExcelYou 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: Then, the Visual Basic Editor will open up. After that, a Module will be created. ➤ Write the following code
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. ➤ Press F5. Practice SectionFor doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself. ConclusionIn 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. How do you automatically copy rows if column contains specific text value in Excel?Select the columns where you will find out cells containing specific text or value.. Click the Kutools > Select > Select Specific Cells.. In the Select Specific Cells dialog box (see screenshot above), ... . Then another Select Specific Cells dialog box comes out. ... . Press Ctrl + C keys in a meanwhile to copy these rows.. How do I copy a cell in Excel based on criteria?Copy Conditional Formatting Using Format Painter. Select the cell (or range of cells) from which you want to copy the conditional formatting.. Click the Home tab.. In the Clipboard group, click on the Format Painter icon.. Select all the cells where you want the copied conditional formatting to be applied.. How do you pull cells that contain specific text?Find cells that contain text. Select the range of cells that you want to search. ... . On the Home tab, in the Editing group, click Find & Select, and then click Find.. In the Find what box, enter the text—or numbers—that you need to find.. How do I copy a cell value based on a condition?Select the result in the worksheet. And then press the shortcut keys “Ctrl + C” on the keyboard to copy the range. Now click another cell in the worksheet. Next press the shortcut keys “Ctrl +V” to paste those cells.
|