Leading zeros often belong to an identifier rather than a quantity. A postal code such as 00123 should not become 123 just because its characters are all digits.
Store identifiers as text
Select the destination cells and choose Format > Number > Plain text before entering or pasting IDs. Test a few examples, including a zero-prefixed value, before importing the entire list.
If Sheets already converted the source to a number, changing its format afterward cannot tell you how many zeros originally existed. Recover the original identifier from an authoritative file or system rather than guessing a length for every record.
Distinguish a display mask from the value
A custom number format such as 00000 can display the numeric value 123 as 00123. This is useful when a numeric column has a known display convention, but its underlying value remains numeric.
For an explicitly defined five-character output, =TEXT(A2,"00000") creates formatted text. It does not establish that five characters are correct for every country or identifier scheme. Avoid imposing one postal-code pattern on a multinational list.
Check downstream uses
Inspect lookup keys, exports, and the receiving application’s import settings. A file that visually shows zeros in Sheets can still be reinterpreted as numbers by another program. Text storage and destination configuration both matter.
Retain the original field and compare exact strings when reconciling records. Do not use arithmetic to test whether two identifiers represent the same entity. For mixed-length IDs, preserve the supplied characters instead of padding every entry to the longest one. The aim is to protect meaning across the workflow, not merely to make the column look consistent.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.