< Go Back

If cell contains this or that

Excel allows you to search for more than one substring in a cell using the COUNTIF function. For different request, we might have to use a combination of functions to return the wanted value.

Formula

=IF(SUM(COUNTIF(A2,{“*this*”,”*that*”})),”yes”,”0”)

How to know if the cell contains this or that

The above formula searches each cell for any instances of the substrings. The formula allows you to indicate what should be returned if the substring is found or not found.

“yes”,”0” in the formula would mean –

if a substring is found, return yes,

Else return 0.

The asterisk sign in the formula is a wildcard that covers for all the instances of a character or characters in a substring.

Example

In the case that you want to look through a list of email addresses to figure out which ones contain ‘gmail’ or ‘org’. This is what to do.

  1.     Make the list of email addresses

Figure 1. Create the email address list

  1.     In the column next to the email address list, give a title and input the formula

Figure 2. Add column heading and formula

  1.     Drag the extend icon to the end of the list

Figure 3. Drag the extend icon to the end of the list

  1.     Final Result.

Figure 4. The result showing the cells that contain Gmail or Org

Notes

  • Notice that the formula has cell A2, which represents the first address on the email address column.
  • Be sure to use the cell number of the list that you have already created – in this case, the email address list, and not the cell number for the cells where the results should appear.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar