How to determine if a cell contains a specific text in Google Sheets

Google Sheets is probably one of the best online tools which is nearly an exact alternate solution to Microsoft Excel. Along with so many features to handle, manipulate and perform simple to complex calculations on any type of dataset, it also provides tools and features that can help us to handle and manipulate text data. Sometimes, we need to find if a specific text lies within a given data range or not and then we have to make some very important decisions based on that.

Therefore, in today’s tutorial we are going to learn some methods which will help us in determining if a cell contains a specific text in Google Sheets. Let’s take a look at the following sales dataset. We’ll follow these simple steps to find if a cell contains the name of a particular city i.e., Lahore then put a YES and if not then NO in the result column.

METHOD 1: Use IF to find exact match

An IF function can be used to find exact matches only within our data range. Let’s assume that we wish to find if a cell contains “Lahore” or not then we’ll follow the simple step shown below.

Step 1 – Use IF function to find an exact match

  • We can use simple IF function to find if a cell contains a specific text or not by using the formula shown below;

=IF(B2 = “Lahore”, “YES”, “NO”)

  • Then this formula can be extended to the whole dataset just by double clicking the fill handle as shown above.

METHOD 2: Use IF with REGEXMATCH to find exact or partial matches

An IF function can be used in conjunction with REGEXMATCH to find exact or partial matches within our data range. Let’s assume that we wish to find if a cell contains an exact match of “Lahore” or not and then we’ll try to find if any cell contains the partial text “la” by following the simple steps shown below.

Step 1 – Use REGEXMATCH with IF function to find an exact match

  • We can use simple REGEXMATCH function to find if a cell contains an exact or partial match of a specific text by using the formula shown below;

=IF(REGEXMATCH(B2, “Lahore”), “YES”, “NO”)

  • Then this formula can be extended to the whole dataset just by double clicking the fill handle as shown above.

Step 2 – Use REGEXMATCH with IF function to find a partial match

  • We can use simple REGEXMATCH function to find if a cell contains an exact or partial match of a specific text by using the formula shown below;

=IF(REGEXMATCH(B2, “la”), “YES”, “NO”)

  • Then this formula can be extended to the whole dataset just by double clicking the fill handle as shown above. We have already highlighted the cell texts which we are looking for.

Step 3 – Use REGEXMATCH with IF function to combine criteria for match

  • We can use simple REGEXMATCH function to find if a cell contains an exact match of a text by using the formula shown below;

=IF(REGEXMATCH(B2, “Lahore|Quetta”), “YES”, “NO”)

  • In this formula, we combined two criteria. We’ll get a YES if the cell contains Lahore or Quetta.
  • Then this formula can be extended to the whole dataset just by double clicking the fill handle as shown above.