I have this column in Google sheets with 11 digits of code that is supposed to be a unique per row. The first 3 digits are the department location, and the last 8 digit is the batch number and it has to be unique.

 sample code:
G0420220001 and K0020220002

I want to make sure that the last 8 digits are unique per row.

In Conditional Formatting, without the first 3 digits, I can use this custom formula


to highlight duplicates, and the 20220002 entry in row 1 will be highlighted if I input another 20220002 in a different row, but K0020220002 and G0420220002, which will appear to be unique at each other will not be highlighted.

I want that these two codes to be highlighted since the last 8 digits of both entries are the same.

1 Answer 1



It seems to me that this formula will do the job:

=ArrayFormula(COUNTIF(RIGHT($A$1:$A,8)*($A$1:$A<>""),RIGHT(A1,8))) > 1

Conditional Formatting

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.