I've got a web application that is exporting its data to a CSV file. Here's one example row of the CSV file in question:

28,"65154",02/21/2013 00:00,"false","0316295","8316012,8315844","MALE"

Since I can't post an image, I'll have to explain the results in Excel. The "0316295" field gets turned into a number and the leading 0 goes away. The "8316012,8315844" gets interpreted as one single number: 83,160,128,315,844. That is, most obviously, not the intended result.

I've seen people recommend a leading single quote for such cases, but that doesn't really work either.

28,"65154",02/21/2013 00:00,"false","'0316295","'8316012,8315844","MALE"

The single quote is visible at all times in the cell in Excel, though if I enter a number with a leading single quote myself, it shows just the intended string and not the single quote with the string.

Importing is not the same as typing, it seems.

Anybody have a solution here?

  • Are you able to import it using LibreOffice or something of that ilk? I've just imported and got : 28 65154 02/21/2013 00:00 false 0316295 8316012,8315844 MALE (if you can read that...?) – FreudianSlip Mar 19 '13 at 16:37
  • This question is similar and offers some good solutions: superuser.com/questions/234997/… – Brad Patton Mar 19 '13 at 16:38
  • @FreudianSlip No, we can't use some 3rd party solution. Users click on the "download CSV" button and the file opens in Excel. That's the environment we're dealing with. Can't really complicate it. – Michael Oryl Mar 19 '13 at 18:07
  • @Brad I read that post earlier looking for solutions. That's where I got the single quote option from, which I really don't like since Excel shows the quote in the cell itself when imported from CSV (even if it doesn't show it if you type the same thing in yourself). – Michael Oryl Mar 19 '13 at 18:09
  • so do you control the web app at least? how are you going to implement the solution transparently from the users? I have updated my answer so it would properly handle values with commas – Alex P. Mar 19 '13 at 19:32

This should work

28,"65154",02/21/2013 00:00,"false",="0316295","=""8316012,8315844""","MALE"
  • No, that mangles the value with two numbers in it. We get ="1234567 with no closing quote. Does work for the simpler numbers, though. – Michael Oryl Mar 19 '13 at 18:12
  • 1
    if you control the web application you could implement export into the SYLK en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK) format. it has much better control of data representation. if not - you could easily make a converter script – Alex P. Mar 19 '13 at 18:46
  • 2
    updated the answer so it would properly handle values with commas – Alex P. Mar 19 '13 at 18:59
  • That handled the comma separated values properly. Thanks. – Michael Oryl Mar 20 '13 at 12:00
  • thanks. This solution solved an issue with importing hexidecimal numbers as well. Excel replaces 00E3 with 0 (0 * 10^3) and displays it as sci notation (0.00E+00). It actually does the same for "00e3" without the =. But ="00e3" works perfectly. (opening a CSV with Excel 2010) – mpag Feb 23 '16 at 20:01

To keep the leading zero(s), you can set the format of that specific column to Text in the Text Import Wizard.

  1. Start the Text Import Wizard by going to the Data tab & clicking From Text.
  2. Select the CSV file you want to import.
  3. Pick Delimited & hit Next.
  4. Uncheck Tab (default). Choose Comma. Make sure Text Qualifier is set to ". Hit Next.
  5. This is where you can specify the formats. Click the header of the column whose leading zero(s) you want to preserve. Select Text. (See image below)

    enter image description here

  6. Hit Finish & then select a suitable location for your data. Sample result:

    enter image description here

Edit: There is another way; have the web app export to a comma-delimited .TXT file instead of .CSV (or just change the file extension of the exported file after saving it). This forces Excel to go through the import wizard when they open the file. As an additional advantage, it reduces the chances that users modify or tamper with your original raw data.

  • 1
    Yes, that's certainly true. It's just not really convenient for the end user when, by comparison, all they have to do is click on the link of the CSV generator on our web app and Excel loads it automatically - albeit with the above mentioned problems. CSV files are set to open in Excel, and Excel doesn't offer any options at that time. It just loads them. I appreciate you pointing that out, though. – Michael Oryl Mar 19 '13 at 18:06
  • 1
    There is another way, but I don't know if it's possible in your case: have the web app export to comma-delimited .TXT instead of .CSV (or just change the file extension of the exported file). This sort of "forces" users to go through the import wizard when they open the file. – Ellesa Mar 19 '13 at 23:18
  • That's a very good point. Thanks for pointing that out. It might be useful in the future. – Michael Oryl Mar 20 '13 at 12:03
  • Text import wizard seems to no longer exist. I was able to import a CSV interpreting everything as text using this method: (1) open a blank worksheet, (2) go to Data > Get Data > From File > From Text/CSV > select csv file, (3) click import (4) change Data Type Detection to Do Not Detect Data Types. – Alcamtar Oct 15 '20 at 17:18

Excel will auto-detect the format of a CSV field unless the CSV column is in this format:

"=""Data Here"""

This also works for Google sheets


Excel treats numeric values starting with a tab character as text.


Will appear as:


| is the cell boundary(for illustration purpose). So, for example, if your system generates the CSV file using utf-8 charset, you can add the &#x0009; (Tab Char in Hex) before your numeric value to force excel to interpret it as text.

  • Remove the space before semi colon in the Hex representation. I don't know how to escape the Hex Tab character in the answer, so I have deliberately typed it wrong. – Madeyedexter Aug 29 '15 at 20:17
  • 1
    I did the escaping. And I learned something new, thank you. I have to add this to my favorite topic for work :) – nixda Aug 29 '15 at 20:29

You'd have to use the "Data -> Get External Data -> Import Text from file" functionality in Excel to get it displayed as text. This is the text import wizard @Kaze mentioned.

The only way to force excel to display numbers as text from the input, without the user having to do anything, is by outputting an excel file and specifying a "custom number format" in it. I recommend using a XLS export library for this, as it doesn't seem as simple as adding a line of config text into a CSV file.


try the clean function for values that are messed up. For example =clean("12345678901234567890"). It works for me for numbers, dates, times, etc. I would caution that since this is not at all the purpose of this function that this could stop working with new versions, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.