A UK company number is eight digits. Open a supplier list in Excel and a fair proportion of them will be seven. Nobody deleted anything. Excel read 01234567, decided it was the number one million two hundred and thirty four thousand five hundred and sixty seven, and dropped the zero because numbers do not start with zero. The cell now reads 1234567 and every Companies House lookup you run against it will fail.
What is actually happening
When Excel opens a CSV it guesses the type of every column. A column containing only digits gets typed as a number, and numeric types have no concept of a leading zero because it carries no mathematical value. The zero is not hidden or formatted away, it is gone from the stored value. Reformatting the cell as text afterwards does not bring it back, because there is nothing left to reformat. This is not a bug and Microsoft will not fix it, because for genuine numbers the behaviour is correct.
Which UK fields this destroys
Company registration numbers are the most damaging, because roughly a tenth of active UK companies have a number starting with zero and every one of them breaks. Sort codes lose their leading zero the same way, which matters a great deal more when you are running a payment file. Phone numbers stored without a country code lose the 0 that makes them dialable. Some postcodes survive because they contain letters, which is the only reason the column is typed as text and left alone.
Why you often do not notice
A seven digit company number still looks plausible. It is only wrong when something tries to use it, and the failure is usually silent. A Companies House API call returns a 404 rather than an error you would investigate. A credit check comes back with no match and gets recorded as a company with no filed accounts. A bank rejects a payment file and the finance team assumes the account details were wrong rather than the sort code. The problem surfaces weeks later as an unexplained data quality issue rather than as a formatting mistake made when a file was opened.
How to stop it happening
The reliable method is to never open the CSV in Excel by double clicking it. Instead open Excel first, then use Data, Get Data, From Text/CSV. That opens the Power Query import dialogue, which lets you set the column type to Text before anything is parsed. Once the file is imported correctly, save it as .xlsx rather than .csv, because a CSV has no type information and the next person to open it will hit exactly the same problem. If you are handed a file that has already lost its zeros, padding back to eight digits is usually safe for company numbers because the format is fixed, but it is guesswork for phone numbers and should not be done blind for sort codes.
Checking a file you have been given
If you have received a list from a client, a broker or a previous system, it is worth checking before you import it anywhere. Our free data health check reads a CSV in your browser and flags company numbers with fewer than eight digits as a critical finding, along with the other things that break quietly: VAT numbers that pass a format check but fail the HMRC checksum, columns holding both day-first and month-first dates, and postcodes in a format no lookup will match. Nothing is uploaded and it takes about a minute.
Check a file before you import it
Our free data health check reads a CSV in your browser and reports the UK specific problems that break business systems. No signup, and the file never leaves your machine.
Run a free data health check