How to check if cell contains partial text in Excel

Microsoft Excel is one of the best tools which provides so many features to handle and manipulate versatile data with its efficient tools and functions. It also provides many built-in tools and features that can help the data analysts in visualizing data and making logical decisions in the best possible ways. Sometimes in the dataset we need to know if the cells in the dataset contain a text completely or partially. This can help us in making some important decisions depending upon the dataset. 

As the title of today’s topic suggests, we are going to learn how to use IF condition to check for partial text matches inside our data. Let’s look at the dataset shown below, we wish to find out for which consignments the payments have either been refunded or partially refunded.

We will apply the following methods to do this. Although one can use other methods too like VBA code, but those are much complex solutions and requires deep knowledge of programming in VBA. So, we’ll discuss the simple to mildly complex solutions using the built-in functions of Excel.

• Use Conditional Formatting to highlight partial matches
• Use IF condition with ISNUMBER & SEARCH functions

Method 1 – Use Conditional Formatting to find partial matches

Step 1 – In the Home Tab locate Conditional Formatting

• Select the whole data values in which we wish to find the partial matches.
• On the Home tab, go to Styles group to locate the Conditional Formatting dropdown.
• Click on the Conditional Formatting dropdown to open new options.
• Click on the Highlight Cell Rules and then select the Text that Contains… option as shown above.

Step 2 – Write the required text to be matched in the dialog box

• This will open up a new dialog box named “Text that Contains”.
• By default, Excel picks up the first cell’s text and apply conditional formatting on the selected data.
• We’ll change the text as per our requirements to “refunded”. Now Excel will highlight all those cells which contain the word refunded. Therefore, all those cells which contain the text partially refunded or refunded will be highlighted.
• So this is how we can find the partial and full matches using conditional formatting in a very simple way.

Method 2 – Use IF with ISNUMBER & SEARCH to find partial matches

Step 1 – Create an appropriate formula to find partial matches

• As discussed earlier, there is not a single formula to find the partial matches using IF function, therefore, we’ll use the IF condition with ISNUMBER & SEARCH functions as follows;

=IF(ISNUMBER(SEARCH(“refunded”,C2)),”YES”, “NO”)

This formula will search the cell C2 for the text refunded for partial or full matches, mainly because of the use of SEARCH function. It returns a number, the position of the searched text in the main string, if a match is found. If a match is not found it returns an error #VALUE. So, by checking the output of SEARCH to be a number or not we can know if the main string contains partial or full match of the searched text or not. The output in case of a partial or full match will be YES otherwise NO.

Step 2 – Implement the formula to find partial matches

• In the image shown above, we have highlighted the target cells which contain our desired text in full form or partial match form. Now let’s implement the formula created in the previous step and see the results.

So the results show a YES in front of those cells which contained “refunded” in full match form or in partial matched form.