Stop Excel from automatically converting certain text values to dates. 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 codestelephone numbersgovernment ID numberswhich 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 iswhere 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 3. 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. ![]() ZWSP, Unicode U+2. B)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. Another optionmight 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. · I created a complex workbook with different column widths and row heights. I am using the same version of Excel on three different devices: home computer. Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date? I'm trying to write a.csv file. Excel Tutorials and Tips. What will you learn today? The tagline here at MrExcel claims that we are “your one stop for Excel solutions”. While there are a lot of. So there are various options. Depending on your requirements/application, one might be better than another.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |