Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Conditional Formatting Help - Highlighting based on the length of data in the cell

Copper Contributor

Greetings

I scan four different barcodes into a single column, formatted as text, into a worksheet.
The barcodes can have the following lengths: 7, 10, 12 & 15 characters long. I need to be able to highlight the cells if they contain duplicate values, but only if they have a length of 7 or 12. I also need to allow for a cell to be skipped. I came up with this Conditional Format formula:
=AND(COUNTIF($A$1:$A$100,A1)>1,LEN(A1)=7)

But I don't know how to incorporate an additional check for the other condition of the cell length of 12.

Conditional Format Ex.jpg

Thank you for your consideration.

 

2 Replies
best response confirmed by lw5812 (Copper Contributor)
Solution

@lw5812 

Use

 

=AND(COUNTIF($A$1:$A$100,A1)>1,OR(LEN(A1)=7,LEN(A1)=12))

ahh... I knew it had to be something simple.
Thank you so much!

1 best response

Accepted Solutions
best response confirmed by lw5812 (Copper Contributor)
Solution

@lw5812 

Use

 

=AND(COUNTIF($A$1:$A$100,A1)>1,OR(LEN(A1)=7,LEN(A1)=12))

View solution in original post