LibreOffice Calc · Data cleanup · Beginner
Fix Numbers Stored as Text in LibreOffice Calc
Convert text numbers into values LibreOffice Calc can calculate using VALUE and ISNUMBER, while preserving codes with leading zeros.
Updated 10 October 2026 · Formula checks: testing standards
When a column looks numeric but Calc does not sum or sort it the way you expect, its cells may contain text strings rather than real numbers. Correct the type only for quantities, prices or measurements—not for ID codes with significant leading zeros.
Check the data type first
If A2 visually contains 1250, enter these formulas into separate empty cells:
=ISTEXT(A2)
=ISNUMBER(A2)
For a text value, expect TRUE and FALSE respectively. Text can come from a CSV imported with a Text column type, leading apostrophes or copied external data.
Convert an actual quantity using VALUE
Enter 1250 as text in A2 by selecting a Text cell format before typing, or by using a text-imported value. Then enter in B2:
=VALUE(A2)
The expected result is numeric 1250. Copy down B for the other rows and confirm that calculations now treat the values as numbers. If you need fixed values rather than formulas, copy the new results and use Paste Special → Values only after checking the conversion.
| Source type | Source display | What to do |
|---|---|---|
| Text quantity | 1250 |
Convert with VALUE |
| Text SKU | 000184 |
Keep as Text |
| Actual number | 1250 |
Do not convert unnecessarily |
Watch decimal separators and locales
A text price such as 1,250.50 can be interpreted differently in different regional settings. VALUE expects numeric text compatible with your document’s number format and locale. If the source uses a different locale, consider importing the raw file with the correct Language setting rather than relying on trial-and-error replacements.
Troubleshooting
#VALUE!appears: inspect spaces, currency symbols, punctuation and decimal separators in the text.- A date appears as a number: Calc stores dates as serial numbers, and you may simply need a Date display format.
- Zeros vanish: identifiers should remain text; see preserve CSV leading zeros.
Related functions: VALUE, COUNT and SUM.
Common questions
Does changing the cell format from Text to Number perform conversion? Not always. Use VALUE or controlled reimport and verify the results.
Can I test whether the repaired column is numeric? Yes. =ISNUMBER(B2) should be TRUE for a converted numeric result.
Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.