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.
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.
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.
- Make the list of email addresses
Figure 1. Create the email address list
- In the column next to the email address list, give a title and input the formula
Figure 2. Add column heading and formula
- Drag the extend icon to the end of the list
Figure 3. Drag the extend icon to the end of the list
- Final Result.
Figure 4. The result showing the cells that contain Gmail or Org
- 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.