Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Check if a cell contains a specific text then return value in another cell in 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.

Check if a cell contains a specific text then return value in another cell


Check if a cell contains a specific text then return value in another cell

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.

Tips:

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


Productivity Tools Recommended
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 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.