Check IF Cell Contains Partial Text in Excel (Formulas)

Sometimes, in my work, I have to check if a cell or a range of cells contains the partial text I’m looking for.

For example, suppose I want to check whether a cell contains the text string “ABC” or not. The cell may have additional strings or characters, but as long as it has the string I’m looking for (which is “ABC”), it fulfills my criteria.

In this article, I will show you a couple of methods to check if a cell contains partial text or not using some simple formulas. I will also show you how to highlight cells that contain the partial text string.

Click here to Download the Example File

Check If a Cell contains Partial Text Match Anywhere

Below, I have a data set where I have product IDs in column A, and I want to check whether the product ID contains the string “ABK” or not.

If it contains that string, I want to return ‘Yes’ in the adjacent cell in column B, and if it does not contain the string, I want it to return ‘No’.

Here is the formula that will do this for me:

=IF(ISNUMBER(SEARCH("ABK",A2)),"Yes","No")

Enter this formula in cell B2 and then copy it for all the cells in the column to get the result for all the product IDs in column A.

Now, let me explain how this formula works.

The SEARCH function of the formula (SEARCH(“ABK”,A2)) looks for the string ‘ABK’ in cell A2, and if it finds it, it returns the numeric value of the starting position where it found that string.

In our formula (in cell B2), this part of the formula returns 1 as it finds the string at the beginning of the product ID. In case it’s not able to find the string, it will return a #VALUE! error.

ISNUMBER(SEARCH(“ABK”,A2)) – this part of the formula returns TRUE in case the result of the SEARCH function is a numeric value, else, it returns a FALSE.

IF function is then used to give us “Yes” if the ISNUMBER formula returns TRUE, and “No”, if the ISNUMBER formula returns FALSE.

Note: The SEARCH function is not case-sensitive. So, it would consider ‘ABK’ and ‘abk’ as the same string. In case you want to do a case-sensitive lookup, you can use the FIND function instead of SEARCH.

Also read: How to Count Cells that Contain Text Strings

Check Cell for Partial Text Match at the Beginning of the Text

In the above example, I wanted to check whether the string I’m looking for is present anywhere in the cell.

But what if I want to check the presence of the string at the beginning of each cell?

This can be done using wildcard characters within the formula.

Below, I have a data set where I have product IDs in column A, and I want to check whether the product ID starts with the string “ABK” or not.

Below is the formula that I can use to do this:

=IF(COUNTIF(A2,"ABK*"),"Yes","No")

Enter this formula in cell B2 and copy it for all the cells in the column.

Now, let’s understand how this formula works.

The above formula uses the following COUNTIF formula (COUNTIF(A2,”ABK*”), where it is looking for the string ‘ABK*’ in cell A2.

Asterisk (*) is a wild card character in Excel that represents any number of characters. So when I use ABK*, I’m looking for any string that starts with ABK and can have any number of characters after it.

The COUNTIF Function returns 1 if it finds that the content in the cell starts with ABK, and 0 if it doesn’t.

The IF function then gives us “Yes” if the result of the COUNTIF function is 1; it returns a “No”

Note: In case there are any leading spaces in the cells in column A, this function will give you incorrect results. If that’s the case, either you first remove these leading spaces, or you use TRIM(A2) instead of A2 in the formula.

Also read: How to Compare Text in Excel

Check Cell for Partial Text Match at the End of the Text

If you want to check for the presence of the lookup string at the end of the text in the cell, you can modify the formula with wildcard characters to do that easily.

Below, I have a data set where I have product IDs in column A, and I want to check if the product ID ends with the string “US “or not.

Here is the formula that will do this for me:

=IF(COUNTIF(A2,"*US"),"Yes","No")

Enter this formula in cell B2 and copy it for all the cells in the column.

In the above formula, I have used *US as the criteria in COUNTIF.

This would make the COUNTIF function check the content of the cell and return 1 only if it finds the string “US” at the end of the cell.

Using an asterisk (*) before the string ‘US’ means that the cell can contain any text string, but as long as it ends with “US”, the COUNTIF formula would return 1.

And if it doesn’t end with the string ‘US’, the COUNTIF formula would return 0.

The IF function then gives us “Yes” if the result of the COUNTIF function is 1; else, it gives us a “No”

Also read: Find the Closest Match in Excel (using Formulas)

Check for Partial Match with AND Condition

You can also combine and check for the presence of two partial strings within a cell using an AND condition.

Below, I have the product IDs in column A, and I want to check if the ID in the cell starts with “ABK” and ends with “US” or not.

So, I need to simultaneously check for two partial matches in the same cell for this condition to be fulfilled.

This can easily be done using an AND function with COUNTIF and wildcard characters (as shown below).

=IF(AND(COUNTIF(A2,"ABK*"),COUNTIF(A2,"*US")),"Yes","No")

Enter this formula in cell B-2 and then apply it to all the other cells in the column.

Now, let me explain how this formula works.

I have used an AND function that checks the result of two separate formulas:

• COUNTIF(A2,”ABK*”) – This returns 1 if the content of the cell starts with the string “ABK”, else, it returns 0
• COUNTIF(A2,”*US”) – This returns 1 if the content of the cell ends with the string “US”, else, it returns 0

The AND function would only return a TRUE when both the COUNTIF functions return a 1 (which means that the string starts ‘ABK’ with and ends with ‘US’). Otherwise, it would give us a FALSE.

And if AND gives us TRUE, the IF function returns the value “Yes” in the cell, else it returns a “No”

Also read: Lookup the Second, the Third, or the Nth Value in Excel

Check for Partial Match with OR Condition

Just like the AND condition shown above, you can also check with an OR condition.

Below, I have some product ID data set in column A, and I want to check if a cell starts with the string ‘ABK’ or it ends with ‘US’. If any or both of these two conditions are met, I want to get ‘Yes’ in the adjacent cell.

Here is the formula that will do this:

=IF(OR(COUNTIF(A2,"ABK*"),COUNTIF(A2,"*US")),"Yes","No")

This above formula checks if the value in cell A2 starts with “ABK” or ends with “US”. If either of the conditions is True, it returns “Yes”; otherwise, it returns “No”.

Below is a step-by-step breakdown of the formula:

• =IF(…): The IF Function checks a condition and returns “Yes” if the condition is true, and “No” if it is false.
• OR(…): The OR function is used to check the two conditions, and if either of the conditions is true, the OR function returns TRUE.
• COUNTIF(A2, “ABK*”): This is the first condition inside the OR function. The COUNTIF function checks if the value in cell A2 begins with “ABK”. The asterisk (*) is a wildcard character that represents any number of characters. So, “ABK*” will match anything that starts with “ABK”, like “ABK-879-US”, “ABK-616-ER”, etc. If it matches, the COUNTIF function returns 1.
• COUNTIF(A2, “*US”): This is the second condition inside the OR function. It checks if the value in cell A2 ends with “US”. Again, the asterisk (*) represents any number of characters before “US”. So, “*US” will match anything that ends with “US”, like “ABK-879-US”, “LDJ-124-US”, etc. If it matches, the COUNTIF function returns 1.
• “Yes”, “No”: These are the values that the IF function will return. If either of the conditions is true (i.e., A2 starts with “ABK” or ends with “US”), the formula returns “Yes”. If neither condition is met, it returns “No”.

Also read: How to Extract a Substring in Excel (Using TEXT Formulas)

Highlight If Cells Contains Partial Text

In all the examples above, I have checked whether the cell contains a partial text string or not and got the result in the adjacent column.

In this section, I will show you how to use conditional formatting to highlight all the cells that contain a required partial text.

Let’s say that I’m using the below data set where I have the product IDs in column A, and I want to highlight all the cells where the product ID contains the string ABK (it could be anywhere in the string).

While I have already covered the formula that would give us the result as Yes or No in the adjacent column, in this case, I am going to put this formula within Conditional Formatting so it helps us highlight the cells that contain that partial text string.

Here are the steps to do this:

1. Select the cells in column A that have the product ID.
2. Click the Home tab.

3. Click on the Conditional Formatting option.

4. Click on the New Rule option. This is going to open the ‘New Formatting Rule’ dialog box.

5. Select the option – Use a formula to determine which cells to format.

6. Enter the below formula in the field:

=ISNUMBER(SEARCH("ABK",A2))

7. Click on the Format button. This will open the ‘Format Cells’ dialog box.

8. Click on the Fill tab and select the color in which you want to highlight the cells.

9. Click OK.

The above steps would apply the conditional formatting on the selected cells, and any cell that has the string “ABK” would be highlighted in the specified color.

You can modify the formula based on your criteria.

Note: Remember that the formula within Conditional Formatting needs to return either a TRUE or a FALSE. It highlights a cell when the condition is met, and the result of the formula is TRUE.

Click here to Download the Example File

So these are some of the formula methods you can use to check if a cell contains partial text matches or not. I’ve covered scenarios where you can check a cell for partial text match anywhere in the cell, in the beginning, or at the end.

I hope you found this Excel tutorial helpful. Feel free to contribute to the article by leaving a comment below.

Other Excel tutorials you may also like:

• How to Compare Two Columns in Excel (for matches & differences)
• How to Extract the First Word from a Text String in Excel
• Lookup and Return Values in an Entire Row/Column in Excel
• Delete Rows Based on a Cell Value (or Condition) in Excel
• Count Unique Values in Excel Using COUNTIF Function
• Count Cells Less than a Value in Excel (COUNTIF Less Than)
• Highlight Rows Based on a Cell Value in Excel
• How to Filter Cells that have Duplicate Text Strings (Words) in it
• SUM Based on Partial Text Match in Excel (SUMIFS)