Established onExcel 365: 15 significant digitsExcel 2016 and later: same limitGoogle Sheets, web: comparable
A spreadsheet decides what each incoming value is, column by column, and decides in favour of numbers. Anything that looks numeric becomes a number, and everything that made it not a number is discarded.
What is lost, and where
Leading zeros. A postal code of 01234 becomes 1234. Any identifier padded to a fixed width loses the padding, which breaks every subsequent match against a system that keeps it.
Digits past the fifteenth. A sixteen-digit card or account number is truncated to fifteen significant digits with zeros after, permanently, because the storage keeps that many and no more.
Long digit strings entirely. Anything long enough is displayed in scientific notation, and where it also exceeded fifteen digits the underlying value has already been damaged.
Anything that looks like a date. Which the entry on autocorrection in the first part covers, and which applies to product codes, gene symbols, fractions and ratios alike.
The distinction that matters most in this whole part
Opening a file and importing a file are different operations with different consequences, and they are one double click apart.
Double-clicking a delimited file opens it with every type guessed and no opportunity to intervene. The damage above is done before anything appears on screen, and it cannot be undone from what is on screen, because the original characters are not stored anywhere.
Importing it deliberately, through the data menu in either product, presents each column and allows its type to be declared before anything is read. A column declared as text arrives as text, with its zeros, its sixteen digits and its slashes intact.
Why the fast route is the default
Because for most files it is right and instant, and the cost of being wrong is invisible. That combination is what makes the behaviour so durable: the fast route succeeds visibly and fails silently, so nothing in ordinary use teaches anybody to stop using it.
The apostrophe, and what it is not
Typing an apostrophe before a value marks the cell as text. The apostrophe is not part of the value and is not shown in the cell.
It is also not exported. A column protected this way, saved back out as a delimited file, produces exactly the same characters as an unprotected one, so the protection lasts precisely as long as the workbook and does not travel to the next system.
Where the fix actually belongs
Upstream. A system exporting identifiers should quote them, or prefix them, or export a format that carries types. Every remedy applied at the spreadsheet is applied after the file has been produced and depends on whoever opens it doing the right thing.
Where the export cannot be changed, the next best place is a repeatable import definition rather than a manual one, which the entry on imports as a process covers: the column types are stated once and applied every time the file is refreshed, by anybody.
Checking a column that should be text
One formula: count the cells in the column that the sheet considers numeric. In a column of identifiers the answer should be zero, and any other answer is the whole problem, named, in a cell.
Put it at the top of the sheet where it will be seen rather than in a corner, and it turns an invisible conversion into a number somebody notices.
The identifiers this affects, listed once
Postal codes, telephone numbers, account and card numbers, product and part codes, book and article identifiers, national insurance and social security numbers, invoice references, batch numbers, and any code composed of digits with leading zeros or a fixed width.
The test is not whether it is made of digits. It is whether arithmetic on it would mean anything, and for every item on that list it would not.
What we cannot verify
The fifteen-digit limit, the conversion behaviours and the import routes are documented by the vendors and demonstrable in any copy. The claim that the apostrophe marker does not survive export was established by testing on current builds rather than found in documentation stating it.
What happens to a code with a letter in it
Nothing, which is the useful contrast. A reference like AB01234 is not numeric, so it survives every route intact, including the double click.
Which is why organisations whose codes contain a letter never encounter this entry, and organisations whose codes are pure digits encounter it constantly. The difference is not in the care taken; it is in a decision somebody made about a coding scheme years earlier.
The one case where the guess is wanted
A file of measurements, where every column genuinely is a number and being read as one is the point. Declaring types for a hundred numeric columns by hand is worse than letting the reader guess.
So the rule is not to import everything deliberately. It is to know which columns are identifiers, declare those, and let the rest be guessed, which is usually two or three columns out of thirty.
In short
- Anything that looks numeric becomes a number and what made it not one is discarded.
- Leading zeros, digits past the fifteenth and anything date-shaped are all lost.
- Opening and importing are different operations one double click apart.
- The fast route succeeds visibly and fails silently, which is why it survives.
- The apostrophe marker lasts as long as the workbook and does not survive export.
- The test is not whether it is digits but whether arithmetic on it would mean anything.