Tech Support banner

Status
Not open for further replies.
1 - 5 of 5 Posts

·
Registered
Joined
·
65 Posts
Discussion Starter · #1 ·
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?
 

·
Premium Member
Joined
·
8,700 Posts
Format the cells as text not numbers then they appear exactly as typed.
I had this problem with a spreadsheet I use at work
 

·
Registered
Joined
·
65 Posts
Discussion Starter · #3 ·
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.
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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
 

·
Premium Member
Joined
·
34 Posts
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.
 
1 - 5 of 5 Posts
Status
Not open for further replies.
Top