# How to prevent Excel from delete leading zeros?



## ljCharlie

I have exported some data off my phpMyAdmin with one field containing leading zeros. As soon as I open in MS Excel, the leading zeros are stripped off. How do I prevent this from happening?


----------



## kodi

Format the cells as text not numbers then they appear exactly as typed.
I had this problem with a spreadsheet I use at work


----------



## ljCharlie

Thank you for the response. However, I believed your suggestion will only work if you are typing in zeros in excel. The problem I have is I already have a .csv file that the column is already in texts with leading zeros but as soon as I open in Excel, the leading zero will be removed. If I open the .csv file in Notepad or Textpad, I can see the leading zeros. So it is something in Excel that will automatically treat leading zeros as nothing and so removed all leading zeros.


----------



## Glaswegian

Hi

Try saving the file as a .txt file. Then when you import the file, the Import Wizard will kick in and you will then have the option of importing the field as a text field (which will retain the leading zero). If a file is saved as a .CSV, when you open it in Excel, it completely bypasses the Import Wizard so you do not have the ability to change/set the format of the incoming data. 

Regards


----------



## allank

ljCharlie said:


> ... If I open the .csv file in Notepad or Textpad, I can see the leading zeros. So it is something in Excel that will automatically treat leading zeros as nothing and so removed all leading zeros.


ljCharlie, another trick is to edit the file to have all leading zeroes preceeded by a single quote ' - this tells Excel to treat the content of the field as text. If the file always has a comma before the leading zero, you can do a replace all, replacing ,0 with ,'0.


----------

