Tablora · Library
Preserve leading zeros in an XLSX
Keep product codes like 000123 intact in an XLSX by exporting every cell as text, so a spreadsheet cannot shorten them to 123.
Digits that are names, not numbers
Item numbers, postal codes, employee IDs and account references are often written with leading zeros: 000123 and 123 are different codes, even though they are the same number. When a CSV is opened in a spreadsheet, a column of digits is usually read as numbers and the zeros disappear. If that file is saved again, the original codes are gone and lookups against other systems stop matching.
The text-safe converter writes every cell of the workbook as an explicit text cell. The spreadsheet receives the characters 000123 and has no number to shorten.
Exporting the id column
- Copy the table with the id column into Table text and pick the matching Delimiter.
- Press Calculate and compare the id column in the preview with the source, counting the zeros in a few values.
- Use Download (XLSX) and open tablora-table.xlsx in your spreadsheet.
- Confirm that the cell shows 000123 and is treated as text, for example by checking that it aligns like text.
Example: id 000123
The page example has a single column named id with the value 000123. The expected cell text is 000123, stored as an explicit text cell rather than a formula or a number. Six characters go in and the same six come out.
The trade-off is that every cell in this workbook is text, including amounts in other columns. If you need sums, convert the amount column inside the spreadsheet after import, and leave the id column alone.
Codes with different lengths are kept as they are too. If some rows read 123 and others 000123, the export shows both exactly as entered, which tells you the source itself is inconsistent; padding them is a separate decision for whoever owns the codes.
Leading zeros in the other tools
- The numeric normalizer refuses to convert an integer that starts with zero, so 000123 stays as text and is counted under Values left for review. That tells you the column probably holds codes.
- CSV downloads from the browser tools keep the zeros in the file, but the program that opens the CSV may still strip them, which is why the XLSX is the safer handoff.
- In the Tablora app, the XLSX export also keeps values that start with a zero followed by a digit as text, even in columns it treats as numbers, so codes like 000123 survive there too.
If the zeros are already gone
A file that has been opened and saved as numbers no longer contains the zeros. Go back to the original export, PDF or paper, not to the spreadsheet copy, and rebuild the column from there.
Original synthetic example
- Input
- id 000123
- Expected cell text
- 000123
- Cell storage
- Explicit text, not a formula or numeric cell