How to handle if cell contains a word, then put a text in another cell?


Excel is a famous spreadsheet-based tool, used to manipulate and process available data. For this provided scenario, the user needs sample string data, to check the presence of the required string. To solve the provided problem statement, the user needs to create a formula by using the search function. The search function will allow the user to search some specific data from the provided cell value, and to generate the results according to the presence and absence of data.

Example 1: To handle a cell that contains a word, by putting data text in another cell by using the user-defined formula:

Step 1:

Firstly, create a sample data in the MS excel worksheet as provided below:

Step 2:

Before, moving further it is better to understand the formula structure:

The explanation for the cell:

  • It is a keyword used in Excel to apply condition-checking blocks.

  • ISNUMBER() is a predefined method of Excel, used to test the cell address. This method accepts an argument. The provided argument will contain expressions that need to be tested with the associated cell name. Results generated by this method are either true or false.

  • SEARCH() method will search the field data, and return the character location as a text string.

  • “Ram” is the column name, to be searched at the provided cell location.

  • “Not available” is the response to be printed in the output column when the searched data is not available.

Step 3:

After that simply go to the B2, cell and type the formula

“=IF(ISNUMBER(SEARCH("Ram",A2)),"Ram","not available")”.

Consider the below-given image for reference:

Step 4:

Press “Enter” key. This will display searched text data to the output column.

Example 2: To handle a cell if cell contains a word, by selecting or highlighting the data by using Kutool tab:

Step 1:

This approach is based on the use of kutool extension. Firstly, user need to open or select the “Kutool” tab. After that Click on the “Editing” section, then choose the “select” section, and click on “Select Specific Cells”.

Step 2:

This will open the “Select Specific Cell” dialog box. Select the range for the “Select cell in this range” label, by hovering mouse over the “string data” column provided within the sample worksheet. In the “selection type” pane, select the “cell” radio button. After that go to the “specific type” option and click on the drop-down column then select “Contains”as shown in below image.

Step 3:

In the front label type the data user wants to search. For this case let’s assume that user want to select “Ram”. After that click on “OK” button.

Step 4:

This will highlight the row values contains same data value and also displays the count of cells that contains matching data.

Conclusion:

This article will explain two ways to search data. The first example allows the user to search data by using the predefined search() method, to obtain the desired output.

The second example is based on the use of the kutool extension. In this example, the user will be able to access the number of rows that contains matching data. Accessing and evaluating data value is easy by using both the provided ways.

Updated on: 17-Apr-2023

698 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements