Check if a cell contains a specific text then return value in another cell in Excel
Kutools for Excel
For example, in Excel, if you have a list of text strings in column A, and now, you may want to check if the cells contain the specific text “multiple”, when cell contains the text “multiple”, another text “Yes” will be displayed in adjacent cell as below screenshot shown. For dealing with this task, you can use a formula, but, here, I will talk about a handy tool – Kutools for Excel, with its Cell contains special text then return value feature, you can quickly get the result without remembering any formulas.
To check a cell if there is a specific text and return a new string or value in another column, please do as this:
1. Click to select a cell where you want to output the result, see screenshot:
2. Then, click Kutools > Formula Helper > Formula Helper, see screenshot:
3. In the Formulas Helper dialog box, please do the following operations:
- Select Text from the Formula Type drop down list;
- In the Choose a formula list box, click to select Cells contains special text then return value option;
- In the Arguments input section, select a cell contains the text string that you want to check from the Cell textbox, then select the cell contains the value that you want to search from the Search value textbox, at last, select a cell contains the new text that you want to return from the Return value box.
- 1). In the Cell textbox, you should change the default absolute cell reference to relative cell reference for dragging the formula correctly.
- 2). In the Search value and Return value textboxes, you can type the specific text (multiple) and return text (Yes) directly as well.
4. Then, click Ok button to get the first result, and then drag the fill handle down to the cells for applying this formula, and you will get the results as below screenshot shown:
Note: This formula is not case sensitive.
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.