Why does Excel remove leading zeros?
Excel can interpret a digit-only identifier as a number, turning 00042 into 42. Numeric values also have a 15-digit precision limit. Store long identifiers as text before entry or import; changing the format afterward cannot recover digits that were already lost.
Source: Microsoft Support — Keeping leading zeros and large numbers.
Decide which columns are identifiers
Folder & Form’s suggested first step is to separate labels from quantities. You may add quantities and prices together, but adding two project references produces no useful answer. Write the intended type next to each column before cleanup. This gives the person preparing the workbook a rule to follow instead of a guess based on how the first few rows look.
- Product code, project reference, and supplier ID: keep as text, including meaningful zeros and punctuation.
- Quantity and unit price: use numeric values only after confirming separators, units, and missing-value rules.
- Dates: agree the source’s date convention before conversion; 04/05 is ambiguous without context.
- Missing information: preserve a blank or an agreed exception flag instead of inserting a plausible value.
Import the identifier column as text
For a CSV in desktop Excel, use Data → From Text/CSV and open the transformation editor. Set identifier columns to Text before loading. If an automatic type-change step has already made them numbers, replace or remove that step first. For manual entry, format empty cells as Text before typing or pasting. Menu names vary by Excel version.
Source: Microsoft Support — Keeping leading zeros and large numbers.
A display format is not a recovery method
A custom number format such as 00000 can make 42 display as 00042. It does not establish which identifier was originally intended. If the source could contain 42, 042, or 00042, check that source. Do not invent a fixed width just because it makes a column look consistent.
Source: Microsoft Support — Keeping leading zeros and large numbers.
Check the handoff after exporting
A CSV is a text exchange format. Opening it in Excel can trigger the receiving app’s data interpretation again. Agree the delimiter and column types with the recipient, then test the actual import method they will use. A workbook looking correct on your screen does not prove a later CSV import will preserve the same identifiers.
Source: Microsoft Support — Import or export text files.
- Compare a short code with zeros, a long code, and a code containing letters against the source.
- Check that blanks remain blanks and that no identifier has become a date.
- Save a separate working copy and keep the unchanged source available for comparison.
- Record the agreed column types in the handoff notes, alongside any unresolved values.
When the problem needs spreadsheet cleanup
A mixed list often needs more than a formatting change. One supplier may use five-character codes while another uses seven; two files may use the same code for different items. Identify the source and matching rules before merging. Folder & Form can review a spreadsheet cleanup scope that preserves identifiers and returns an exception list for uncertain matches.
A few useful answers
Can I put the zeros back after they disappear?
Only when the original source or a confirmed identifier rule tells you what belongs there. A column-wide padding rule can create incorrect identifiers when lengths vary.
Should every spreadsheet column be text?
No. Store identifiers as text and use appropriate numeric types for values that need calculations. Agree the purpose of each column before transforming it.
What should I send for a cleanup quote?
Describe the columns, approximate row count, source formats, and the problem. Do not paste the data into the form. A private sample arrangement can follow if the scope needs one.