How to Check If Cell Contains Specific Text in Excel

In this article, we will learn How to Check If Cell Contains Specific Text in Excel.

Scenario :

Identify particular text in a cell or different word in given cells. For example finding the department ID from a database. I think you must have thought to do it manually but time constraint. You are here at the right place to learn How to check if a cell contains specific text.

Generic formula:

The text you are looking for can either be exact or case insensitive. Case insensitive means formula looks for AG can return ag, Ag, AG or aG.

Case insensitive formula:

=ISNUMBER(SEARCH(find_text,within_text))

find_text : text to find

within_text : to find in text

Case sensitive formula:

=ISNUMBER(FIND(find_text,within_text))

find_text : text to find

within_text : to find in text

Note:

The above formulas will return True or False. Use the IF function with formula to return YES or NO.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some employees to look up by the given department Id. Employee id has name, department id and particular id. Here lookup text is in column D and within text is in Column C.

Use the formula:

=ISNUMBER(SEARCH(D4,C4))

As you can see the formula finds aB when you looked for

AB using the Search function. Copy the formula to the rest of the cells using the Ctrl + D or dragging it down from the right bottom (tiny box) of the applied cell.

As you can see we found all the given department id employees using the above method. Now we will check if all cells contain specific text.

Another Example: (Case Sensitive)

Here we have been given a list of Employee Id information in column A and the look up department is "XG". Here we need to find the department "XG" in all cells. "XG" must be exact as there are two different departments that go by "Xg". So check only "XG" in all cells and return "Yes" if found and return "No" If not.

Use the formula:

=ISNUMBER(FIND(D4,C4))

As you can see First employee doesn't belong to "XG" so the formula returns "No" using the FIND function. Copy the formula to the rest of the cells using the Ctrl + D or dragging it down from the right bottom (tiny box) of the applied cell.

As you can see there is only one employee in "XG" department. This formula is useful wherever the database contains multiple information of a row in one cell.

Check if cell Matches multiple text

In the above example we lookup one given text in cells. If we have multiple texts then we use the SUMPRODUCT function, This formula returns TRUE/FALSE as per the value found/ Not found.

Use the formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(,A1)))>0

Learn more about this formula. Follow this link How to Check if a string contains one of many texts in Excel.

Here are all the observational notes using the above explained formulas in Excel
Notes :

Use the "find_text" in quotes when using hard coded values. Or else use cell reference as explained in the first example.
SEARCH is case insensitive function whereas FIND is the case Sensitive dunction.
Use the IF function, if you want to get the result in required forms like YES/NO or Found/Notfound.

Hope this article about How to Check If Cell Contains Specific Text in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

Searching a String for a Specific Substring in Excel : Find cells if cell contains given word in Excel using the FIND or SEARCH function.

Highlight cells that contain specific text : Highlight cells if cell contains given word in Excel using the formula under Conditional formatting

How to Check if a string contains one of many texts in Excel : lookup cells if cell contains from given multiple words in Excel using the FIND or SEARCH function.

Count Cells that contain specific text : Count number of cells if cell contains given text using one formula in Excel.

How to lookup cells having certain text and returns the Certain Text in Excel : find cells if cell contains certain text and returns required results using the IF function in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.