How to Highlight Duplicate 7 or 12 Character Barcodes in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets conditional formatting rule that highlights duplicate barcode values only when the barcode contains 7 or 12 characters, while ignoring duplicates of other lengths and blank cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing a barcode worksheet and identifying duplicate codes that meet a specific length requirement.
- Observed behavior
- A standard duplicate-value rule marks duplicates of all lengths and can include blank cells; the user needs length-specific highlighting.
Check whether barcode values must remain as text to preserve leading zeros, and identify the exact worksheet range and the top-left cell in that range before creating the rule.
Create a Formula-Based Conditional Formatting Rule for 7- or 12-Character Duplicate Barcodes
Use this when you want WPS Spreadsheets to highlight only duplicate barcodes whose length is exactly 7 or 12 characters.
This rule combines COUNTIF to identify duplicate values and LEN to accept only barcodes with 7 or 12 characters. Blank cells do not match the length condition, so they remain unhighlighted.
Select the barcode cells, for example A1:A100. Make a note of the top-left cell in the selection, such as A1.
Go to the Home tab and click Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter =AND(COUNTIF($A$1:$A$100,A1)>1,OR(LEN(A1)=7,LEN(A1)=12)). Replace $A$1:$A$100 with your absolute range and A1 with your top-left cell.
Click Format, choose a fill color or font style, then click OK to preview and OK again to apply the rule.
Use a Helper Column to Identify Duplicate 7- or 12-Character Barcodes
Use this method if you prefer to see TRUE or FALSE results alongside the barcode list and then filter or sort manually.
Highlight Duplicate 7 or 12-Character Barcodes Directly in WPS Spreadsheets
WPS Spreadsheets can complete this task with a formula-based conditional formatting rule. Open your barcode worksheet, apply the COUNTIF and LEN formula, and WPS will highlight only qualifying duplicate codes while ignoring blanks and other lengths.
- 1. Open your barcode file: Launch WPS Spreadsheets and open the .xlsx, .xls, or .csv worksheet that contains the barcode list.
- 2. Select the barcode range: Select the cells you want to check, such as A1:A100, and note the top-left cell.
- 3. Create the conditional formatting rule: Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =AND(COUNTIF($A$1:$A$100,A1)>1,OR(LEN(A1)=7,LEN(A1)=12)).
- 4. Choose a format and apply: Click Format to set the highlight, then click OK to apply the rule to the selected range.
Frequently Asked Questions
What does the formula =AND(COUNTIF($A$1:$A$100,A1)>1,OR(LEN(A1)=7,LEN(A1)=12)) do?
COUNTIF counts how many times the value in A1 appears in A1:A100, and LEN checks the character count. The formula highlights the cell only when the value appears more than once and its length is exactly 7 or 12 characters.
How do I prevent blank cells from being highlighted?
Leave the LEN condition in place: a blank cell has a length of 0, so OR(LEN(A1)=7,LEN(A1)=12) returns FALSE and the blank cell is not highlighted.
What should I change if my barcode range starts at B2 instead of A1?
Replace $A$1:$A$100 with the absolute range, such as $B$2:$B$500, and replace A1 with B2 in the formula: =AND(COUNTIF($B$2:$B$500,B2)>1,OR(LEN(B2)=7,LEN(B2)=12)).
Does this conditional formatting rule work for barcode values stored as numbers?
Yes, LEN can count digits in numeric values, but leading zeros are not stored unless the cells are formatted as text. If leading zeros matter, keep the barcode column as text before applying the rule.
Can I use the same rule in Microsoft Excel files opened in WPS Spreadsheets?
Yes, the COUNTIF and LEN formula is compatible with Excel and WPS Spreadsheets, so the rule works on .xlsx and .xls barcode lists.
