这个问题仍然存在于Mac Office 2011和Office 2013，我无法阻止它发生。这似乎是很基本的事情。

在我的例子中，我有诸如“1 - 2”和“7 - 12”的值，在CSV中正确地用倒逗号括起来，这将自动转换为excel中的日期，如果您尝试随后将其转换为纯文本，您将得到日期的数字表示形式，如43768。此外，它将条形码和EAN编号中的大数字重新格式化为123E+数字，再次无法转换回来。

我发现谷歌驱动器的谷歌表不能将数字转换为日期。条形码中每3个字符有一个逗号，但这些很容易被删除。它处理csv非常好，特别是在处理MAC / Windows csv时。

说不定还能救人。

这周我刚刚遇到了这个惯例，它似乎是一个很好的方法，但我在任何地方都找不到它。有人熟悉吗?你能举出出处吗?我没有找了几个小时，但我希望有人会认识到这种方法。

例1:=("012345678905")显示为012345678905

例2:=("1954-12-12")显示为1954-12-12，而不是12/12/1954。

I know this is an old question, but the problem is not going away soon. CSV files are easy to generate from most programming languages, rather small, human-readable in a crunch with a plain text editor, and ubiquitous. The problem is not only with dates in text fields, but anything numeric also gets converted from text to numbers. A couple of examples where this is problematic: ZIP/postal codes telephone numbers government ID numbers which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric. Or the value contains characters that can be confused with mathematical operators (as in dates: /, -). Two cases that I can think of that the "prepending =" solution, as mentioned previously, might not be ideal is where the file might be imported into a program other than MS Excel (MS Word's Mail Merge function comes to mind), where human-readability might be important. My hack to work around this If one pre/appends a non-numeric and/or non-date character in the value, the value will be recognized as text and not converted. A non-printing character would be good as it will not alter the displayed value. However, the plain old space character (\s, ASCII 32) doesn't work for this as it gets chopped off by Excel and then the value still gets converted. But there are various other printing and non-printing space characters that will work well. The easiest however is to append (add after) the simple tab character (\t, ASCII 9). Benefits of this approach: Available from keyboard or with an easy-to-remember ASCII code (9), It doesn't bother the importation, Normally does not bother Mail Merge results (depending on the template layout - but normally it just adds a wide space at the end of a line). (If this is however a problem, look at other characters e.g. the zero-width space (ZWSP, Unicode U+200B) is not a big hindrance when viewing the CSV in Notepad (etc), and could be removed by find/replace in Excel (or Notepad etc). You don't need to import the CSV, but can simply double-click to open the CSV in Excel. If there's a reason you don't want to use the tab, look in an Unicode table for something else suitable. Another option might be to generate XML files, for which a certain format also is accepted for import by newer MS Excel versions, and which allows a lot more options similar to .XLS format, but I don't have experience with this. So there are various options. Depending on your requirements/application, one might be better than another. Addition It needs to be said that newer versions (2013+) of MS Excel don't open the CSV in spreadsheet format any more - one more speedbump in one's workflow making Excel less useful... At least, instructions exist for getting around it. See e.g. this Stackoverflow: How to correctly display .csv files within Excel 2013? .

这是我知道如何在不搞乱文件本身的情况下完成这一点的唯一方法。就像使用Excel一样，我用头在桌子上敲了几个小时才学会这一点。

Change the .csv file extension to .txt; this will stop Excel from auto-converting the file when it's opened. Here's how I do it: open Excel to a blank worksheet, close the blank sheet, then File => Open and choose your file with the .txt extension. This forces Excel to open the "Text Import Wizard" where it'll ask you questions about how you want it to interpret the file. First you choose your delimiter (comma, tab, etc...), then (here's the important part) you choose a set columns of columns and select the formatting. If you want exactly what's in the file then choose "Text" and Excel will display just what's between the delimiters.

在微软Office 2016版本中，这仍然是一个问题，这让我们这些研究基因名称的人感到不安，如MARC1、MARCH1、SEPT1等。 我发现最实用的解决方案是在R中生成一个“。csv”文件，然后将与Excel用户打开/共享:

以文本(记事本)形式打开CSV文件 复制它(ctrl+a, ctrl+c)。 粘贴到一个新的excel表格中-它将全部粘贴在一列作为长文本字符串。 选择/选择此列。 转到Data-“Text to columns…”，在打开的窗口中选择“delimited”(下一步)。检查“逗号”被标记(标记它将显示数据与下面列的分离)(下一步)，在这个窗口中，您可以选择您想要的列并将其标记为文本(而不是通用)(完成)。

HTH

None of the solutions offered here is a good solution. It may work for individual cases, but only if you're in control of the final display. Take my example: my work produces list of products they sell to retail. This is in CSV format and contain part-codes, some of them start with zero's, set by manufacturers (not under our control). Take away the leading zeroes and you may actually match another product. Retail customers want the list in CSV format because of back-end processing programs, that are also out of our control and different per customer, so we cannot change the format of the CSV files. No prefixed'=', nor added tabs. The data in the raw CSV files is correct; it's when customers open those files in Excel the problems start. And many customers are not really computer savvy. They can just about open and save an email attachment. We are thinking of providing the data in two slightly different formats: one as Excel Friendly (using the options suggested above by adding a TAB, the other one as the 'master'. But this may be wishful thinking as some customers will not understand why we need to do this. Meanwhile we continue to keep explaining why they sometimes see 'wrong' data in their spreadsheets. Until Microsoft makes a proper change I see no proper resolution to this, as long as one has no control over how end-users use the files.