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.
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:
- Select B2:B15 and click Format > Conditional formatting.
- Select Custom Formula Is.
- Enter the above formula in the given field.
- Choose a formatting style.
- Click Done.
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
- Highlight Duplicates in Google Sheets
- Highlight Visible Duplicates in Google Sheets
- Skip Duplicates in Min | Small Value Highlighting Row Wise in Google Sheets
- Highlight Max Value Leaving Duplicates in Row Wise in Google Sheets
- Highlight Duplicate Values Based on Occurrence Days in Google Sheets
- How to Highlight Conditional Duplicates in Google Sheets
- How to Highlight Adjacent Duplicates in Google Sheets
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))=5Replace C2 with the corresponding cell reference and “abcpq” with your choice of letters.
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
Hi, Joe,
I suggest you search and substitute them. Then go for highlighting.
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?
Hi, Elizabeth,
You should modify the wildcard (asterisk) position accordingly. Please try this formula.
=if(A1<>"",Countif(A$1:A,"*"&right(A1,7)) > 1)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!
Hi, TassaDarK,
At present, there is no such option within the Conditional Formatting in Google Sheets.
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?
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))>0The LEFT and LEN combination returns the value trimming 4 characters from the end of the string in cell G6.