Why is an identifier different from a quantity?
Microsoft explains that Excel can remove leading zeros from numeric-looking text and describes text-based import handling and custom display formats. Its guidance distinguishes preserving underlying data from changing how a number appears. Feature availability varies by Excel version, so confirm the actual application and workflow.
Returnables Agency is independent and is not affiliated with Microsoft. Follow the authority's required file format and the bid owner's approved workbook process. Do not change a supplied identifier scheme to suit a local preference.
Sources: Microsoft: Microsoft Support: Keeping leading zeros and large numbers
Which checks belong in an import and export record?
Keep a controlled copy of the original schedule. Identify columns that are codes rather than arithmetic inputs. Quantities and authorised rates may need numerical types; the aim is to preserve each field's meaning, not turn the entire workbook into text.
| Stage | Identifier evidence | Release question |
|---|---|---|
| Source | Exact authority item ID and revision | Are leading zeros meaningful? |
| Import | Declared type for the ID column | Was conversion controlled before loading? |
| Workbook | Underlying identifier and row association | Does display match stored data? |
| Join or lookup | Exact key and match result | Were distinct IDs collapsed? |
| Export | Actual final file reopened by its intended workflow | Did conversion recur? |
| Approval | Exceptions and authorised corrections | Was the original scheme retained? |
How can two rows accidentally become one key?
Illustrative example. A fictional authority schedule defines 0012 and 12 as two different item IDs. Importing both as numerical values can produce the same value, 12. A later lookup based on that value cannot reliably distinguish the two source rows, even if a custom format displays one cell as 0012.
The example assumes the authority treats the strings as distinct; it does not assert that every tender does. Changing the cell format after information was lost cannot establish which original ID belonged to which row. Return to the controlled source and obtain an authorised correction rather than inventing missing zeros.
How should the final file be checked?
Use exact identifiers from the controlled source to reconcile the submitted rows and their response associations. Include IDs with leading zeros and any long or mixed-character codes in the check. A matching row count or grand total cannot detect every changed identifier.
Reopen the actual final export through the intended workflow and verify it, rather than relying only on a screenshot of the workbook. Retain the approved correction and exact issued copy. This check complements formula and print-scope review; it does not approve prices or tender content.
Common questions
What else should the bid team know?
Does a custom zero format always preserve the source identifier?
No. Display formatting and underlying text are different.
Can missing zeros always be recovered by changing the format?
No. Reconcile against the controlled source.
Should quantities also be changed to text?
Not automatically. Preserve the intended meaning and type of each field.