If Cell Contains Specific Text Then Return Value in Excel (3 Easy Methods)

When working with Excel, you may often need to check whether a cell contains specific text and return a corresponding value. For example, if a cell contains the word “multiple”, you may want to return “Yes”; otherwise, return a blank or another value.

However, Excel does not provide a direct built-in feature for this task. Instead, you need to rely on formulas, VBA, or third-party tools. In this article, we will introduce three practical methods to help you achieve this: using Excel formulas, VBA, and a much easier solution with Kutools for Excel.

• Return Value If Cell Contains Specific Text Using Formula
• Return Value If Cell Contains Specific Text Instantly with Kutools
• Return Value If Cell Contains Specific Text Using User-defined Function

Return Value If Cell Contains Specific Text Using Excel Formula

Excel formulas provide a flexible way to determine whether a cell contains specific text. Although there is no single function designed specifically for this task, you can combine functions like IF, SEARCH, and ISNUMBER to achieve it.

For example, in the worksheet below, if a cell in column A contains the text "multiple", we want to return "Yes" in column B.

Steps:

1. Select a blank cell where you want to display the result (e.g., B3).
2. Enter the following formula and press Enter to get the first result.

   =IF(ISNUMBER(SEARCH("multiple",A2)),"Yes","")

3. Drag the fill handle down to apply the formula to other cells.

   

Formula Explanation:

• SEARCH("multiple", A3) → checks if the text "multiple" exists in the cell A3.
• ISNUMBER(...) → returns TRUE if found.
• IF(...) → returns "Yes" if TRUE, otherwise blank.

Return Value If Cell Contains Specific Text Instantly with Kutools for Excel

If you are looking for a faster and more user-friendly solution, Kutools for Excel provides a built-in formula tool that allows you to achieve this task without writing any formulas.

With Kutools Formula Helper, you can complete the entire process through a visual interface, making it ideal for beginners and users who want to save time.

Steps:

1. Select a cell to return the result.
2. Click Kutools > Formula Helper > Text > Cell contains specific text then return value.

   

3. In the Formula Helper dialog box, configure the arguments:
   • Cell: Select the cell where you want to check the text.
   • Search value: Enter the text you want to search (e.g., "multiple").
   • Return value: Enter the value to return (e.g., "Yes").
4. Click OK.

   

Result:

The result is returned instantly for the selected cell, with the formula generated automatically—no formulas to remember. You can then drag the fill handle down to apply it to the remaining cells.

Why Use Kutools for This Task

• No need to remember formulas.
• No syntax errors.
• Visual and intuitive interface.
• Perfect for beginners and non-technical users.

Return Value If Cell Contains Specific Text Using User-defined Function

If you need a more customizable or automated solution, VBA can also be used to perform this task. However, this method requires basic programming knowledge and is less suitable for most users.

Steps to Use VBA:

1. Press Alt + F11 to open the VBA editor.
2. Click Insert > Module.
3. Paste the code into the module window.
   VBA Code: Return value if cell contains specific text

   Function ContainsText(rng As Range, txt As String) As String
       If InStr(1, rng.Value, txt, vbTextCompare) > 0 Then
           ContainsText = "Yes"
       Else
           ContainsText = ""
       End If
   End Function

   

4. Press Alt + Q to return to Excel. Then enter the following formula into the cell where you want to return result:

   =ContainsText(A2,"multiple")

5. Press Enter and drag down to apply.

   

Limitations:

• Requires VBA knowledge.
• Macros must be enabled.
• Not suitable for most users.

Conclusion

Returning a value if a cell contains specific text is a common task in Excel, and there are several ways to achieve it.

• The Excel formula method is flexible and widely used, but requires understanding nested functions.
• The VBA method offers more control, but is not suitable for most users.
• Kutools for Excel provides the easiest solution, allowing you to complete the task without writing any formulas.

If you want a quick, reliable, and error-free way to handle this task, using Kutools Formula Helper is the most efficient choice.