Count 26-Digit Serial Numbers as Text in Excel Using WPS Office
Question details
The user needs to store and count 26-digit serial numbers in a spreadsheet without losing digits after Excel's 15-digit precision limit.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is managing data containing 26-digit serial numbers and needs to count exact matches in a range.
- Observed behavior
- When entered as numbers, 26-digit serial numbers are rounded and trailing digits become zeros, causing incorrect counts and text matching failures.
Confirm whether the serial numbers have already been entered as numbers. If they have, the lost digits cannot be recovered; collect the original identifiers and re-enter them as text before counting.
Format the Serial Number Cells as Text and Count Exact Matches
Use this method before entering serial numbers so WPS Office stores the full 26 digits as text and the count formula compares exact values.
Excel-compatible spreadsheets keep only 15 significant digits for numeric values. Any 26-digit serial number entered as a number will be rounded and lose the trailing digits, so the values must remain text.
Select the range where the serial numbers will be entered, press Ctrl+1, click Text in the Number tab, and choose OK.
Type each serial number into the formatted cells. You can also type an apostrophe before the value, such as '28928813202104020000003265, to force text entry.
In an empty cell, enter =SUMPRODUCT(--(A2:A13="28928813202104020000003265")) and press Enter. Adjust A2:A13 to your actual data range.
Use the Number Format Drop-Down for Blank Ranges
Use this method when you have blank target cells and prefer the ribbon instead of a keyboard shortcut.
Count Long Serial Numbers Accurately in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel files and text formatting, so you can enter 26-digit serial numbers as text, count exact matches with SUMPRODUCT, and avoid losing digits beyond Excel's 15-digit precision limit.
- 1. Open your workbook in WPS Office: Launch WPS Office and open the .xlsx file that contains or will contain the serial number data.
- 2. Format the serial number column as Text: Select the cells, press Ctrl+1, choose Text, and click OK before entering the identifiers.
- 3. Enter the full 26-digit values: Type each serial number exactly, or prefix each value with an apostrophe to keep it as text.
- 4. Count with the SUMPRODUCT formula: Use =SUMPRODUCT(--(A2:A13="28928813202104020000003265")) to return the exact match count.
Frequently Asked Questions
Why does Excel change the last digits of my 26-digit serial number?
Excel-compatible spreadsheets store numbers with 15 significant digits. Beyond that, digits are rounded and trailing digits become zero. To keep a 26-digit serial number unchanged, format the cell as Text or type an apostrophe before it.
How do I format cells as text in WPS Office Spreadsheets?
Select the range, press Ctrl+1, click Text under the Number tab, and choose OK. You can also choose Text from the Number Format drop-down on the Home tab.
What formula counts exact 26-digit serial numbers stored as text?
Use =SUMPRODUCT(--(A2:A13="28928813202104020000003265")). Adjust the range to your data; the formula returns the number of cells that match the full text string.
Can I recover lost digits from serial numbers that were already rounded?
No. Once digits beyond the 15-digit limit have been lost, Excel cannot restore them. Re-enter the original serial numbers as text or import them as text.
Does WPS Office keep long serial numbers when saving .xlsx files?
Yes, if the cells are formatted as Text, WPS Office saves and shares the .xlsx workbook without converting these text identifiers to numbers.
