This post will guide you how to return a value if a cell contains a certain number or text string in Excel. How do I check if a Cell contains specific text and then return another specific text in another cell with a formula in Excel.

1. Return Value If Cell Contains Certain Value Using a Formula

Assuming that you want to check if a given Cell such as B1 contains a text string “excel”, if True, returns another text string “learning excel” in Cell C1. How to achieve it. You can use a formula based on the IF function, the ISNUMBER function and the SEARCH function to achieve the result of return a value if Cell contains a specific value. Just like this:

=IF(ISNUMBER(SEARCH("excel",B1)),"learning excel","")

Type this formula into the formula box of cell C1, and then press Enter key in your keyboard.

You will see that the text string “learning excel” will be returned in the Cell C1.

And if you want to check the range of cells B1:B4, you need to drag the AutoFill Handle down to other cells to apply this formula.

2. Return Value If Cell Contains a Specific Text Using a User-Defined Function

Now, let’s dive into the second method, which involves creating a custom function using VBA. We’ll guide you through the step-by-step process of writing the VBA code, making it accessible even if you’re new to coding.

Step1: Press Alt + F11 to open the Visual Basic for Applications editor.

Step2: Right-click on any item in the Project Explorer, go to Insert, and select Module.

Step3: Copy and paste the following VBA code into the module:

Function CheckText(cellValue As Range, targetText As String) As String
    If InStr(1, cellValue.Value, targetText, vbTextCompare) > 0 Then
        CheckText = "learning excel"
    Else
        CheckText = ""
    End If
End Function

Note: This code defines a function named CheckText that checks if a cell contains specific text.

Step4: Close the VBA editor.

Step5: Now, you can use your new function in Excel. In a cell, enter the formula:

=CheckText(B1, "excel")

Note: Replace “excel” with your target text and adjust the cell reference as needed.

Step6: After entering the formula, press Enter. The cell will display “learning excel” if the specified text is found; otherwise, it will be blank.

3. Video: Return Value If Cell Contains Certain Value

This tutorial video will show you how to return a value if a cell contains specific text in Excel. This is a common task in data processing, and we’re here to guide you through two effective methods to achieve this goal. Let’s explore both methods – one using a formula and the other with a User-Defined Function (UDF) in VBA.

https://youtu.be/x0PKgR5blzI

4. Related Functions

• Excel SEARCH function
  The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
• Excel ISNUMBER function
  The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…