A cell displaying 12 can hold a real number or the text characters 1 and 2. That distinction affects sums, comparisons, sorting and lookup keys. The right repair depends on whether the field represents a quantity or an identifier.
First decide what the field means
| Source value | Intended meaning | Recommended data type |
|---|---|---|
12 units |
A quantity to add or multiply | Number |
00123 SKU |
A product identifier where zeros matter | Text |
07512 postal segment |
A code, not arithmetic | Text |
12.50 selling price |
Numeric currency amount | Number, with suitable display formatting |
2026-10-11 |
Possibly an ISO date, but confirm source meaning | Date or text, depending on purpose |
Do not automatically convert every digit-only field. For identifiers, preserving the original text is usually the correct result even if the spreadsheet shows a text warning.
Diagnose with ISNUMBER
Suppose A2 contains text 12, and A3 contains numeric 12. In LibreOffice Calc:
=ISNUMBER(A2)
=ISNUMBER(A3)
Expected results: FALSE and TRUE. That tells you how the application currently stores those cells; the number format or right alignment alone is not proof. You can make a separate helper cell with =VALUE(A2) to convert an appropriate numeric text input. The expected numeric result is 12 if the current locale accepts the string.
Do not use VALUE for SKUs such as 00123 unless losing the two leading zeros is truly desired. A numeric result is 123 and cannot remember how many zeros the source originally had.
Fix a calculation without destroying source data
- Keep a copy of the source or import a second copy for testing.
- Put
=ISNUMBER(A2)in a helper column to identify actual numeric cells. - For a genuine quantity stored as text, use
VALUEin a separate column or a documented conversion tool in your spreadsheet. - Confirm the converted output and a sample sum before replacing the original quantity field.
- Leave identifier columns as text, especially when you use them in lookups.
- Reconcile row totals against the original export before using results for business decisions.
For a mixed dataset, don’t assume one formula will correctly convert all currencies, separators and negative-number styles. A string such as 1.234,50 might be valid in one locale and misread in another.
A lookup-key trap
A reference table keyed by text 00123 should generally use text 00123 as the lookup value. Converting the search key to numeric 123 may appear to solve one record while creating silent mismatches elsewhere. For the fuller diagnostic, use Why lookups return no match.
Prevent the problem on the next import
In a CSV import preview, select identifier columns as Text before committing the import. File CSV itself has no durable spreadsheet-type metadata, so import settings matter. See LibreOffice: preserve leading zeros, PlanMaker CSV imports and the CSV Import Doctor.
Common questions
Can custom number formatting restore lost leading zeros? It can make a number look like a fixed-width code, but it does not recreate the original text or tell you whether the source had that width. Confirm the original export.
Why doesn’t SUM count some visible numbers? The imported values may be text, or the range may contain other nonnumeric data. Diagnose the data types before coercing them.
Does every spreadsheet use identical coercion rules? No. The formula and import behavior can vary by software, locale and release. Verify in the application that will use the data.
Verification note: The example 12, 00123 and boolean outcomes were independently reasoned using documented Calc functions and ordinary numeric semantics. They were not executed in every application. No application screenshots are included.
Further reading: LibreOffice CSV documentation, LibreOffice ISNUMBER and VALUE.