Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a real problem with Excel in csv files where it is formatting numbers in certain ways and I cannot seem to get Excel to display the data right no matter what I do.

Imagine I have a colum called "Product Reference". Within that column I have many IDs of the type:

  • 001145
  • 55666
  • 02133

Whenever I open the Excel file it formats these numbers to be:

  • 1145
  • 55666
  • 2133

Which is totally wrong. When I highlight the column and format it as text the previous 0's at the beginning of the numbers do not return.

In an attempt to solve this I even made the csv file a text file first and then imported the file into Excel and specifically told it to not format the numbers by making it text format within the import but it still formats the numbers wrong when I close it and open it again due to the fact that csv cannot define types so Excel auto saves the file as pure garbage.

Has anyone found a way around this and could help me out?


share|improve this question
As far as I know, the only way to get Excel to faithfully render a CSV is to import it as text, the way you described. – Excellll Aug 31 '11 at 14:45
You don't have to make the CSV a text file though. The Import from Text File feature works for CSV files too. You just have to specify that you have comma delimiters. – Excellll Aug 31 '11 at 14:48
up vote 8 down vote accepted

You need to specify in the csv file that it is text. You do this by putting your number in quotes and preceeding with and equal sign, eg:


The easiest way to do this would be to do a find-replace on , with ",=", replacing end of lines (you might need to use an advanced editor like Notepad++ for this) with "\r\n=" and doing the start and end of the file manually.

share|improve this answer
Nice :) I'll give this a try and report back – Sammaye Aug 31 '11 at 14:52
Worked perfect thanks for the help :D – Sammaye Aug 31 '11 at 15:04
  1. Change the file extension from ".csv" to ".txt" on the file you are having problems with.
  2. Open excel by itself. Don't click on the file to open it.
  3. Click "Open", change the file types to look for from "All Excel Files" to "All Files"
  4. Find your file and click open. Since Excel doesn't auto format txt, it will then take you thru the wizard.
  5. Makes sure the "Delimited" radio button is selected. Click the "next" button.
  6. Check the "comma" delimiter box and keep hitting the "next" button until you SEE (but don't click on it) the finish button.
  7. scroll thru each column in the spreadsheet, highlight each column(s) you want to manually format and change the format to "text".
  8. Click the finish button.
share|improve this answer
This is a good, hassle-free way to solve the problem. – Jack May 19 '15 at 17:43
You saved my day! – dinesh ygv Dec 7 '15 at 12:36

Install OpenOffice from Oracle and do the same with OpenOffice's CSV format and it works.

I've had this problem in Windows 7 Pro with Excel 2010. I used to be able to copy Outlook contacts to Excel, save as CSV and import to Gmail and Android (the mobile numbers with leading zeros and + signs).

share|improve this answer

Right-click on the cells containing your data, and click on the Format cells... menu entry. Then, in the first tab ("Number"), change the category from General to Text. This will prevent Excel from automatically formatting those cells as numbers, and thus removing the leading zeroes (which are meaningless in the context of numerical analysis, but very meaningful when dealing with product IDs).

If the data is being imported and parsed as numbers, you need to use the Data Import wizard, and set each column type to "Text" instead of "General". Then, do the same thing with the cells, and then save your Excel workbook.

share|improve this answer
CSV files do not retain formatting. This isn't going to help. – Excellll Aug 31 '11 at 14:38
@Excellll obviously, it's just a bunch of comma-seperated ASCII characters. By default, Excel marks cell formats as General and attempts to interpret the value (numbers, dates, anything) so it can be used with formulas (as plain text usually cannot). You need to manually tell Excel to interpret the value as plain-text so it does not convert it automatically. – Breakthrough Aug 31 '11 at 14:44
Yea would work if Excel didn't "auto-save" the formatting of fields :( I need Excel to act the same way as any other CSV opener/Programming Language. – Sammaye Aug 31 '11 at 14:46
I have tried that, Excel defaults it back when you open the converted Excel file. So I import from TXT to Excel and tell it in there to display as text, close the file and open it and Excel defaults back to number format for the fields hiding their true value and I am right back at sq one. – Sammaye Aug 31 '11 at 14:47
@Breakthrough, sorry I didn't express what I meant. As you said in your comment, when a CSV is opened in Excel, Excel will apply General formatting to all the data. At this point, the text (i.e. leading zeros) that is actually saved in the CSV file cannot be retrieved by Excel. The point is that once you've opened the CSV in Excel, it's too late to change the formatting. – Excellll Aug 31 '11 at 15:20

click on the cell you want to keep the leading zero's on then: right click > Format cells > custom > 0 > type in the total number that needs to be in the cell such as: If I need 00001234 then i will put 00000000 as the number of zero's, all that is telling it to do is put a total number of 8 in the cell, i already have 1234 so it will make up the difference with 4 more leading zero's.

I use this daily for importing spreadsheets.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .