Cell content formatted somehow and messing with my formatting formula

Hi guys,

I got an excel file from an extract of a database contained in another application.
It looks like the entries have been formatted in someway.
What is displayed is not the same thing as the cell content.

For example:
In cell A1, I see "0165723130"
but when I select the cell, it shows "165723130" in the formula bar.

As the external application has added a zero at the end of each entry and that each entry is supposed to be only 9 digits, I was looking at removing the last zero by using formula in B1=LEFT(A1,9)
But it gives 165723130 instead of 016572123. Just like if it was using what is in the formula bar when selecting A1.
In the format list, the cell is shown as "Custom" format. Whatever I changed it to, it does not keep the leading 0.
The only solution I found is to copy all value to the block note and remove the last 0, one by one... But there are more than 2000 entries

I am not quite sure what sort of evil magic is happening to my cells. Any suggestion will be greatly appreciated.

Ignore what it looks like, that's irrelevant.
If LEFT(A1,9) gives 165723130 then the value in the cell is 165723130 since that value is nine digits long, ie there is no leading zero.

If you want 016572313 use

=MID(A1+10000000000,2,9)