Excel Contains Function

One commonly practised task in Excel is checking whether a cell contains a particular value or not. But the irony is that no default inbuilt function can help you look for a value or number, specific text, or any other value apart from the non-empty cell.

Now the question arises of how to check if a cell contains a specific text or not? Don't worry. Though Microsoft Excel has not introduced any contains function, you can use the in-built ISNUMBER and SEARCH function to quickly check if a cell contains the text in your worksheet.

Now the question arises of how to check if a cell contains a specific text or not? Don't worry. Though Microsoft Excel has not introduced any contains function, you can use the in-built ISNUMBER and SEARCH function to quickly check if a cell contains the text in your worksheet.

Before moving ahead with the steps let's have a brief introduction about SEARCH and ISNUMBER function.

What is Search Function?

"The inbuilt SEARCH function in Excel returns the location of a substring inside the specified string. This function returns the position of the first character of find_text inside within_text. Unlike FIND function, the SEARCH function doesn't permit the wildcards, and it is not case-sensitive (the upper case and lower case letters are considered different)."

Syntax

Parameter

• find_text (required) - This parameter represents the text to find.
• within_text (required)- This parameter represents the text to search within.
• start_num - [optional] - This parameter represents the starting position in the text to search. If the user doesn't provide any value, by default it takes 1.

Return

This function returns the location of a substring another a main string.

What is ISNumber Function?

"The inbuilt ISNUMBER function in Excel will return a Boolean TRUE if a cell contains the specified number, and FALSE if it doesn't not contain the same. You can incorporate the ISNUMBER to check that a cell contains a numeric value, or that the output of another function is a number."

Syntax

Parameter

Value (required): This parameter represents the specified value to check.

Return

This function will return a Boolean TRUE if a cell contains the specified number, and FALSE if it doesn't not contain the same. You can incorporate the ISNUMBER to check that a cell contains a numeric value, or that the output of another function is a number.

Steps to implement CONTAINS function in Excel

We are given a data below where in the substring column we are given substring to check whether they are contained in the main string or not.

To match if a cell contains specific text, we can utilize the SEARCH function combined with the Excel ISNUMBER function. In simple terms, we always look for a substring in the specific text, and text defines text in the cell you are testing.

Following are the steps to use the SEARCH and ISNUMBER function in Excel:

1. To look whether the text contains our substring firstly we will use the SEARCH FUNCTION. Start the formula with equals to '=' followed by the SEARCH function. In the parameters, it will ask for the main string and the substring to find.
   Formula becomes: =SEARCH (B2, A2)
   
   Explanation: The above function will return the position of the substring "Sea"at which it found in the main string. Therefore, it has returned 39 as an output.
2. Next, we will incorporate the ISNUMBER function in the above formula. This function will return a Boolean TRUE if the given cell contains a number else it will return FALSE.
   Formula becomes: =ISNUMBER(SEARCH(B2,A2))
   
   Explanation: Since the cell A2 contains the string "sea", therefore the search function will return the position in numbers. Because the cell C2 contains number it will return TRUE indicating that the main string contains the substring.
3. Drag the formula down the cells using the drag option '+' present at the right most side of the rectangular box. You will get the following output
   

NOTE: You can also check if a cell contains specific text, without showing the substring. Make sure to encircle the substring in double quotation marks.

Case sensitive version

If you want the formula to be case-sensitive, you can replace the SEARCH function with the FIND function.

What is Find Function?

"The inbuilt Excel FIND function returns the position (in a numeric format) of the specified substring in the given main string. If the text is found, it returns the numeric position; if the text is not found, the function returns a #VALUE error."

Syntax

Parameter

• find_text (required) - This parameter represents the text to find.
• within_text (required)- This parameter represents the text to search within.
• start_num - [optional] - This parameter represents the starting position in the text to search. If the user doesn't provide any value, by default it takes 1.

Return

This function returns a numeric value representing the location of location of the substring inside the main string.

STEPs to incorporate the CONTAIN function using FIND

The FIND function will return if the position of the substring is found in the main string and it returns the #VALUE! error if the value is not found. We use incorporate this characteristic to check whether the substring is found by using the ISNUMBER function to "catch" valid numeric positions.

Following are the steps to use the FIND and ISNUMBER function in Excel:

1. To look whether the text contains our substring firstly we will use the FIND FUNCTION. Start the formula with equals to '=' followed by the FIND function. In the parameters, it will ask for the main find_string and the main_string to find.
   Formula becomes: =FIND (B2, A2)
   
   Explanation: The above function will return an error since the "S" in Sea is capital in the substring whereas in the main string it is small.
2. Next, we will incorporate the ISNUMBER function in the above formula. This function will return a Boolean TRUE if the given cell contains a number else it will return FALSE.
   Formula becomes: =ISNUMBER(FIND(B2,A2))
   
   Explanation: The FIND is a case sensation; therefore, it won't match the occurrence of substring Sea with the main string and will return #VALUE error function. Because cell C2 does not contain any number, it will return FALSE
3. Drag the formula down the cells using the drag option '+' present at the right most side of the rectangular box. You will get the following output.
   
4. You can also customise the above formula by using the IF function. Incorporate the IF Function. In the below formula (CASE-SENSITIVE IF formula), if excel matches the exact substring with the main string, it will return "Found"; else, if there is no exact occurrence, it will return "Not Found".

With specific search string

While working with Excel, we often require testing a cell for a specific hardcoded search substring. Excel extends the capabilities to match a series of zero or more characters with IF and COUNTIF functions.

Before moving ahead with the steps let's have a brief introduction about COUNTIF function.

What is COUNTIF Function?

"The COUNTIF function in Excel counts the number of cells that meet a criterion. This function is used to count the number of cells that contain dates, numbers, and text."

Syntax

Parameter

Range (required) - This parameter represents the text to find.

Criteria(required)- This parameter represents the criteria that checks and filter the cells that should be counted.

Return

This function returns a number representing cells counted.

STEPs to incorporate the CONTAIN function using IF and COUNTIF

You can also use IF and COUNTIF in Excel to check if a cell contains specific text. However, unlike the SEARCH function, the COUNTIF function is also case-insensitive (uppercase and lowercase letters are considered same).

Following are the steps to use the IF and COUNTIF function in Excel:

1. To look whether the text contains our substring firstly we will use the SEARCH FUNCTION. Start the formula with equals to '=' followed by the SEARCH function. In the parameters, it will ask for the main string and the substring to find.
   Formula becomes: =(COUNTIF(A2,"*"&B2&"*"))
   
   Explanation: The above function will reduce to =(COUNTIF(A2,"*Sea*"). An asterisk in the formula (*) matches a series of zero or more characters in the specified text. If it found the occurrence of the substring it will return the count. Since in our text we have only one occurrence of Sea, it will return 1.
2. Next, we will incorporate the IF function in the above formula. The below formula will return FOUND, if the given cell contains a number else it will return NOT FOUND.
   Formula becomes: =IF(COUNTIF(A2,"*"&B2&"*"),"Found", "Not Found")
   
   Explanation: The FIND is a case sensation; therefore, it won't match the occurrence of substring Sea with the main string and will return #VALUE error function. Because cell C2 does not contain any number, it will return FALSE.
3. Drag the formula down the cells using the drag option '+' present at the right most side of the rectangular box. You will get the following output.
   

That's it! Now you can easily check whether a cell contains specific text or not. Incorporate the above formulas per your requirement and be ahead of your peer group.