Not the job you came for? Tell me what you need
Why it happens, why the usual advice doesn't undo it, and which values you can actually get back. Some you can't, and knowing which is the useful part.
You opened a CSV, and a column of codes that read 0012,
0800 or 0412345678 now reads 12,
800 and 412345678.
Excel decided that column was numeric while it was loading the file. A
number does not have a leading zero — there is no difference between
012 and 12 arithmetically — so the padding was
discarded as it was read. Nothing warned you, because from Excel's point
of view nothing went wrong.
The important part: this is not a display setting. The zeros are not hidden, waiting for the right format. They were never loaded. Save the file and they are gone from the file too.
| Postcodes |
Every NT postcode starts with a zero. 0800 is Darwin,
0872 covers a large part of the Territory. They become
800 and 872.
|
| Mobile and landline numbers |
0412345678 becomes 412345678. Stored as a
number it may also acquire scientific notation or a thousands
separator, which is the same loss wearing a different hat.
|
| BSBs |
062000 becomes 62000. This is the one that
matters most, and there is a warning about it further down.
|
| Bank account numbers | Frequently padded, and — unlike a BSB — not a fixed length, which is what makes them unrecoverable. |
| ACNs | Nine digits, and plenty of them begin with a zero. |
| Invoice, product and customer codes |
0012, 007, 00045-A. Whatever
scheme your system uses, Excel only sees digits.
|
All three of these are widely recommended. None of them recovers a zero that has already been dropped.
| Format Cells → Text | Applied afterwards, this changes how the cell is treated from now on. The characters are already gone, so it has nothing to restore. It is worth doing before you type, and useless after. |
A custom number format like 0000 |
This is the most common advice and the most misleading, because it
genuinely looks fixed on screen. The cell displays
0012 and stores the number 12. Anything
that reads the file rather than looking at it — an import screen,
Power Query, another program, this site — still sees
12. It is a mask, not a value.
|
=TEXT(A1,"0000") and similar padding |
This does produce real text, and it is the right tool once you
already know the correct width. It cannot tell you what the
width was. Padding everything to four characters turns a genuine
12 into 0012 just as happily as it repairs
one.
|
This is the question worth answering honestly. A dropped zero is only recoverable when the format has a fixed, known length. If it doesn't, the information is gone — the file no longer records how many zeros there were, and neither does anything else you have.
| Postcode |
Recoverable. Always four digits, so
800 can only have been 0800.
|
| Mobile number |
Recoverable. Ten digits beginning 04.
|
| ACN | Recoverable. Always nine digits. |
| BSB | Arithmetically recoverable, and check it anyway. Six digits, so the padding is unambiguous — but a BSB is the one field here where being wrong sends real money to a stranger you cannot get it back from. Restore it from the remittance advice or the supplier's invoice, not from a formula. |
| Bank account number |
Not recoverable. Length varies between banks, so
there is no way to know whether 12345 was
012345, 0012345 or itself.
|
| Invoice, product and customer codes | Not recoverable from the spreadsheet. Re-export from the system that issued them. If the scheme is genuinely fixed width — every code in the system is six characters — then it is recoverable, but confirm that rather than assuming it from the rows in front of you. |
Where it isn't recoverable, the honest move is to go back to the source export rather than to reconstruct. A padded code that has been guessed at is worse than one that is obviously missing, because it looks finished.
The only reliable answer is to never let Excel guess. Double-clicking a CSV is what invokes the guess, so:
| Best | Don't open it at all. If you are exporting from one system to import into another, send the file straight across. Every trip through a spreadsheet is a chance for it to be helpful. |
| In Excel |
Open Excel first with a blank workbook, then
Data → From Text/CSV, and in the preview set
every code column's type to Text before loading.
Renaming the file from .csv to .txt also
forces the import dialog rather than a silent open.
|
| In Google Sheets | File → Import, and turn off Convert text to numbers, dates and formulas. Left on, it does the same thing Excel does. |
Then check a padded value at the bottom of the file as well as the top. Type detection is decided from a sample of the rows, so a column can survive the first hundred and change character further down.
Losing the zeros rarely breaks anything immediately. It breaks the next thing you do with the file, which is why it is usually found late.
| Two files stop matching |
Your system says 0012 and the file from the bank, the
POS or the customer says 12. Every lookup misses, and
the report reads as a list of missing transactions rather than as a
formatting problem.
|
| Imports are rejected, or worse, accepted | A rejected import tells you. An accepted one with a code that no longer matches anything sets up a reconciliation problem for somebody else to find. |
| Payment files | A BSB is six digits. Five digits is not a BSB, and a bank file cannot ask what you meant. |
This site was built around this bug, so it is worth saying exactly what it does. When a column contains whole values that are padded with zeros, it is read as text and kept as text — any padded value in the column is enough, not a majority, because treating a code as text costs nothing while treating it as a number destroys something you cannot get back. Postcodes, phone numbers, BSBs and invoice references come out the way they went in.
It cannot restore zeros that were already lost before the file reached us — nothing can, for the values in the table above. What it can do is stop the next round of it, and tell you what it decided about every column so you can check.
It's free, nothing is stored, and the file never leaves Australia. Clean a spreadsheet, or see what a file contains column by column first.