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.
Table of Contents
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:
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.
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
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:
Note: Replace “excel” with your target text and adjust the cell reference as needed.
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.
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])…