How to Highlight Duplicate 7- or 20-Digit Barcodes in Excel
Question details
The user needs a method to identify and highlight duplicate barcodes that are exactly 7 or 20 digits long without encountering Excel's numeric precision limitations.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying conditional formatting to a dataset containing long numeric barcodes to find specific duplicates.
- Observed behavior
- Excel limits numeric precision to 15 significant digits, replacing subsequent digits with zeros. This causes unique 20-digit barcodes to appear identical and triggers false duplicates in conditional formatting.
Ensure your barcode data is imported or formatted strictly as Text before pasting or typing the values. If Excel has already converted 20-digit barcodes into scientific notation or rounded them to 15 digits, you must reimport the data from the original source.
Apply Conditional Formatting with COUNTIF and LEN Formulas
Use a custom formula in conditional formatting to accurately detect duplicates for exactly 7- or 20-digit long text strings.
To prevent Excel from rounding 20-digit barcodes, ensure the column is formatted as text. You can add a leading apostrophe before the barcode or format the cell as Text before pasting. Then, use a combination of COUNTIF and LEN to evaluate the strings and highlight duplicates.
Highlight the range of cells containing your barcodes (for example, B1:B250).
Go to the Home tab, click on Conditional Formatting, and select New Rule from the dropdown menu.
In the dialog box, select the option labeled 'Use a formula to determine which cells to format'.
Input the following formula: =AND(COUNTIF($B$1:$B$250,B1)>1,OR(LEN(B1)=7,LEN(B1)=20)). Make sure to adjust the cell references to match your specific data range.
Click the Format button, go to the Fill tab, choose a highlight color, and click OK twice to apply the rule.
Easily Manage and Highlight Long Barcodes in WPS Spreadsheet
WPS Spreadsheet provides a lightweight and free environment to work with large datasets. You can seamlessly import text data and apply custom conditional formatting rules to highlight duplicate barcodes without data loss.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the file containing your barcode data.
- 2. Select the Target Range: Highlight the column or specific cells with your imported barcode text.
- 3. Create a New Formatting Rule: Navigate to Home > Conditional Formatting > New Rule.
- 4. Input the Duplicate Check Formula: Select 'Use a formula to format cells' and input =AND(COUNTIF($B$1:$B$250,B1)>1,OR(LEN(B1)=7,LEN(B1)=20)).
- 5. Set Highlight Color: Click Format to set your preferred fill color, then click OK to save and apply the rule.
Frequently Asked Questions
Why do my 20-digit barcodes look identical in Excel?
Excel has a 15-digit limit for numerical precision. Any numbers past the 15th digit are changed to zero, causing different 20-digit barcodes to look identical and trigger false duplicates unless they are formatted as Text before entry.
Can I recover the lost digits if Excel already converted my barcodes?
No. Once Excel truncates the numbers past 15 digits and saves the changes, the lost digits cannot be recovered through formatting. You must reimport the data from the original source and ensure the column is formatted as Text beforehand.
Why doesn't the FILTER function work for finding duplicates in my Excel version?
The FILTER function is a dynamic array formula that is only available in Microsoft 365 and Excel 2021 or newer. If you are using Excel 2016 desktop, you will get a 'The function isn't valid' error and must use Conditional Formatting or helper columns instead.
How do I force Excel to treat a long number as text?
You can format empty cells as Text before typing or pasting your data, or type a single leading apostrophe (') before the number to force Excel to read it strictly as a text string.
