How to keep 00123 and 123 as separate product codes
Keep product codes such as 00123 separate from 123 when the leading zeros matter. Check the stored values and choose the right type before joining files.
If 00123 and 123 identify different products, set both matching columns to Text before joining them. Text preserves the leading zeros that distinguish the codes. If the source has already lost those zeros, correct it before importing again.
A displayed zero may not be stored data
First check whether the zeros are stored in the cell or only shown by Excel's number format. A cell can display 00123 while storing the number 123. Compare the imported values with the intended product codes to find out which case you have.
In this fictional registry, 00123 is Part A and 123 is Part B. The transaction sheet contains the number 123. Removing zeros to force a match could assign Part B's transaction to Part A.
Set both code columns to Text before joining
- Load Registry and Transactions. Decide whether each code is an identifier or a number intended for arithmetic.
- Click the type badge for Registered code and select Text.
- Set Transaction code to Text as well. Numeric 123 is interpreted as the text “123.”
- Place Registry on the left and Transactions on the right. Join the code columns and select All rows from the left sheet.
- Check unmatched codes in Result preview. Keeping all registry rows lets you see codes that would disappear in a matching-only join.
An unmatched code can be the correct result
| Registered code | Product name | Transaction code | Quantity |
|---|---|---|---|
| 00123 | Part A | — | — |
| 123 | Part B | 123 | 4 |
| 12.5 | Part C | 12.5 | 2 |
The result retains three registry rows. Part A has no matching transaction because “00123” and “123” are different text values.
Can JOIN SHEET add the missing leading zeros?
Selecting Text preserves the value available to JOIN SHEET; it does not fill in zeros. If every code must have five digits and 123 means 00123, restore the five-digit codes in the source and import it again. Check the code rule first so you do not change valid shorter identifiers.
Long identifiers rounded or truncated before import cannot be restored this way either. Preserve identifiers as text when exporting them, where possible. Check a few codes with leading zeros and look for hidden spaces before relying on the result.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
XLookup to lookup data from 2 sheets with leading zeros — Reddit r/excel
The first source asks about losing leading zeros when reopening a CSV. This fictional registry focuses on joining identifiers whose original text is still available.
