Highlight Partial Matching Duplicates in Google Sheets

Published on

Google Sheets has no built-in conditional formatting rule to highlight partial matching duplicates in a column. However, this is a common highlighting task.

The good news is that we can create custom rules using formulas. However, when you start writing a formula to highlight partial matching duplicates, you may face several roadblocks.

Example showing partial matching duplicates highlighted in Google Sheets

Assume you have a list of drawing numbers in a column, along with their revisions.

For example, if a drawing number is “A-101” and there is a revision “A-101 Rev. 00,” both should be considered duplicates. You might be able to highlight the first occurrence using a COUNTIF formula with a wildcard match.

If the list is in B2:B15, you can use the following formula:

=AND(B2<>"", COUNTIF($B$2:$B$15, "*"&B2&"*")>1)

The issue with this approach is that it will highlight only the drawing numbers, not the revisions.

To fully highlight partial matching duplicates, use the following custom formula in Google Sheets:

=ArrayFormula(
   LET(
      range, $B$2:$B$15, 
      cell, B2, 
      test1, COUNT(IFERROR(SEARCH(cell, range))), 
      test2, COUNT(IFERROR(SEARCH(IF(range="", NA(), range), cell))), 
      test3, cell<>"", 
      AND(OR(test1>1, test2>1), test3)
   )
)

Example of Highlighting Partial Matching Duplicates in Google Sheets

Assume the range to highlight is B2:B15:

  1. Select B2:B15 and click Format > Conditional formatting.
  2. Select Custom Formula Is.
  3. Enter the above formula in the given field.
  4. Choose a formatting style.
  5. Click Done.
Custom rule settings for partial matches in the Conditional Format panel in Google Sheets

This will highlight all partial matching duplicates in the range.

When using this formula, replace $B$2:$B$15 with the actual range and B2 with the first cell in the range.

Formula Explanation

The formula consists of three main components: test1, test2, and test3, with SEARCH as the key function:

SEARCH(search_for, text_to_search, [starting_at])

Roles of Each Test

  • test1 – COUNT(IFERROR(SEARCH(cell, range)))
    • Searches for the current cell value in the range and returns an array of numbers (matches) or errors.
    • The COUNT function counts the number of matches.
    • Example: If the range is {A-101, A-101 Rev. 00, A-101 Rev. 01} and the current value is A-101, the result is 3.
  • test2 – COUNT(IFERROR(SEARCH(IF(range=””, NA(), range), cell)))
    • Searches for all values in the range within the current cell.
    • Prevents false matches from empty cells by converting them to #N/A errors.
    • Example: If the range is {A-101, A-101 Rev. 00, A-101 Rev. 01} and the current value is A-101 Rev. 00, the result is 2.
  • test3 – cell<>””
    • Ensures the formula does not highlight empty cells.

Final Formula Expression

AND(OR(test1>1, test2>1), test3)
  • OR(test1>1, test2>1) – If either condition is TRUE, the formula identifies a partial matching duplicate.
  • AND prevents highlighting empty cells.

Additional Tip

Assume you have drawing numbers like these:

  • A-101 – Architectural Floor Plan (Level 1)
  • A-101 – Architectural Floor Plan (Level 2)

These may look like partial matching duplicates, but they are not—they are distinct values.

However, they would be considered partial matching duplicates if formatted as:

  • A-101 – Architectural Floor Plan
  • A-101 – Architectural Floor Plan (Level 2)

or

  • A-101
  • A-101 – Architectural Floor Plan (Level 2)

If the values are distinct but follow a specific pattern, consider splitting them or using functions like LEFT or REGEXEXTRACT to extract parts for highlighting.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

11 COMMENTS

  1. Hi,

    Is there a way to highlight cells with ANY 5 matching letters, regardless of where they are in the cell, and ignoring LEFT or RIGHT?

    • Hi, Moe,

      Try this rule.

      =counta(unique(split(regexreplace(regexreplace(C2,"[^abcpq]",""),".", "$0,"),","),1))=5

      Replace C2 with the corresponding cell reference and “abcpq” with your choice of letters.

  2. Hi,

    Is there a way to highlight different address variations?

    Ex.
    335 S Los robles ave
    335 south Los robles ave

    2603 RAE DELL AVE APT 1
    2603 RAE DELL AVE # 1

    57 Westland ave apt A
    57 Westland Ave Unit 1

  3. Hi, I am trying to adapt this function to the right side of a string of numbers in google docs.

    But when I change the function to RIGHT instead of LEFT, it does nothing. Any suggestions?

  4. Hi!

    I know, in Excel, you can do this.

    But, can Google Sheet apply a conditional format to a partial text in a cell? NOT to the entire cell, only a portion of the text inside the cell (the one that matches the rule).

    Thanks!

  5. I have one column with date, next with event names, last is with names.

    How to highlight duplicate names (last column) by 1st and 2nd columns using conditional formatting in google sheets?

  6. Is there a way to match the contents of the cell for all but the last four characters of one? (e.g., “Image D53” and “Image D53.png”?

    • Hi, Melinda Campbell,

      It’s possible! In my example below, the value “Image D53.png” is in cell G6. Let’s see how to match as per your requirement of leaving the last 4 characters from this value.

      =match("Image D53",left(G6,len(G6)-4))>0

      The LEFT and LEN combination returns the value trimming 4 characters from the end of the string in cell G6.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.