if cell contains specific text then return value

Here is the the Excel formula to check if cell contains specific text then return value. Often, we need to search for a text in a cell or range of cells in the Excel, return if it contains a specific text or string. Following is the simple formula to find specific text in given cells and return its values.

If Cell Contains Specific Text Then Return Value

Following is the Excel formula to find If Cell Contains Specific Text Then Return Value. This example used IF and COUNTIF functions to check for a value in a Cell and return required value.

=IF(COUNTIF(A1,"*Specific Text*"),"Yes","No")

You can see that we are counting the cells with specific text and returning the Value ‘Yes’ if found, ‘No’ if not found in the Cell.

If Cell Contains Specific Text Then Return Value – Using COUNTIF Function

We can return the Cell value if using the same functions. You can also change the Specific Text to a Cell reference if you wants to make the function more dynamic.

=IF(COUNTIF(A1,"*"&B2&"*"),A1,"")

the above function returns the Cell Value if a specific text found in the Cell, otherwise it returns Blank.

if cell contains specific text then return value

If Cell Contains Specific Text Then Return Value – Using SEARCH Function

You can also achieve this by using Search Function. Below is the Excel formula to find If Cell Contains Specific Text Then Return Value. In our example, we have input data in Cell A2:A12 and We will Return the Values in Range B2:B12.

Here the Excel formula to Return Value If Cell Contains Specific Text :

=IFERROR(IF(SEARCH(B2,A2,1)>0,A2,0),"")

Values Passed in the SEARCH Function

  • Parameter 1: B2=”find_text”, the text where you can replace with the specific text to be searched in another text
  • Parameter 2: A2=”within_text”, where you will search for ‘find_text’
  • Parameter 3: 1=Character number which you want start you search in within_text from the left.

if cell contains specific text then return value

Formula to Determine If Cell Contains Specific Text

Here is the simple formula to check if cell contains specific text or not. You can search for a text and return the required values (for example: Yes, No), if finding text existing in within cell.

=IF(COUNTIF(A2,"*Specific Text*"),"Yes, it is Found", "Not Found")

Here COUNTIF function determines if a specific text exist in a Cell. And the IF function return “Yes” if Found.

Formula to Determine If Cell Contains Certain Text

You can use the following formula to Determine If Cell Contains Certain Text. You can change any text in the Cell B1 and you can see the result instantly.

=IF(COUNTIF(A1,"*Certain Text*"),"TRUE","FALSE")

We can make this more dynamic by passing certain text using a cell as shown below:

=IF(COUNTIF(A1,"*" &B1 &"*"),"TRUE","FALSE")

Excel VBA If Cell Contains Certain Text

Here is the Excel VBA to check if Cell contains certain text. You can change the cell reference to any cell and specificText to any text.

Sub MacroToCheckIfCellContainsSpecificText_vba()

Set Cell = Cell(1, 4) ' You can change this
specificText = "Specific Text" ' You can change this

If UCase(Cell.Value) Like "*" & UCase(specificText) & "*" Then
MsgBox "Found"
Else
MsgBox "Not Found"
End If
End Sub

Share This Story, Choose Your Platform!

Leave A Comment

Excelx.com is a Excel Blog by PNRao to provide latest tips of Excel and help you to Quickly Learn and up-to date with Excel Concepts. We help you to know – how to do things in Excel with Clean and Easy Excel Tips, Shortcuts, Reference, Tutorials and New and Quick Tricks and Methods.

We Provide Free Online Tutorials, Formulas, Templates, Dashboards and Macros to excel in Microsoft Excel. We cover basic to advanced concepts and share creative ideas, Excel tips and Shortcuts with Related Example Files. You Can refer verity of examples covered on latest tools and techniques in Microsoft Excel Applications.

Related Posts