Stop Excel from automatically converting certain text values to dates

Question

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 from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date   I ve tried putting all of my text fields  including the one that looks like a date  within double quotes  but that has no effect

User · Answer

EXCEL 2016 and later  actually I have not tried in older versions    Open new blank page Go to tab  Data  Click  From Text CSV  and choose your csv file Check in preview whether your data is correct  In  ase when some column is converted to date click  edit  and then select type Text by clicking on calendar in head of column Click  Close  amp  Load

User · Answer

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

User · Answer

EASIEST SOLUTION I just figured this out today    Open in Word Replace all hyphens with en dashes Save and Close Open in Excel   Once you are done editing  you can always open it back up in Word again to replace the en dashes with hyphens again

User · Answer

CSV - comma separated values  Just create edit through text editor instead of xls xlsx exel  In editing you can set date in required format and it must be kept intact  This is assuming same file is then getting processed programatically

User · Answer

What I have done for this same problem was to add the following before each csv value        and one double quote after each CSV value  before opening the file in Excel   Take the following values for example   012345 00198475   These should be altered before opening in Excel to        012345       00198475    After you do this  every cell value appears as a formula in Excel and so won t be formatted as a number  date  etc   For example  a value of 012345 appears as     012345

User · Answer

Hi I have the same issue    I write this vbscipt to create another CSV file  The new CSV file will have a space in font of each field  so excel will understand it as text    So you create a  vbs file with the code below  for example Modify CSV vbs   save and close it  Drag and Drop your original file to your vbscript file  It will create a new file with  SPACE ADDED  to file name in the same location   Set objArgs   WScript Arguments  Set objFso   createobject  scripting filesystemobject    dim objTextFile dim arrStr   an array to hold the text content dim sLine    holding text to write to new file   Looping through all dropped file For t   0 to objArgs Count - 1       Input Path     inPath   objFso GetFile wscript arguments item t          OutPut Path     outPath   replace inPath  objFso GetFileName inPath   left objFso GetFileName inPath   InStrRev objFso GetFileName inPath       - 1   amp    SPACE ADDED csv          Read the file     set objTextFile   objFso OpenTextFile inPath         Now Creating the file can overwrite exiting file     set aNewFile   objFso CreateTextFile outPath  True       aNewFile Close         Open the file to appending data     set aNewFile   objFso OpenTextFile outPath  8   2 Open for writing 8 for appending        Reading data and writing it to new file     Do while NOT objTextFile AtEndOfStream         arrStr   split objTextFile ReadLine               sLine        Clear previous data          For i lbound arrStr  to ubound arrStr              sLine   sLine         arrStr i                Next           Writing data to new file         aNewFile WriteLine left sLine  len sLine -1   Get rid of that extra comma from the loop       Loop       Closing new file     aNewFile Close    Next   This is for next file  set aNewFile nothing set objFso   nothing set objArgs   nothing

User · Answer

Okay found a simple way to do this in Excel 2003 through 2007   Open a blank xls workbook   Then go to Data menu  import external data   Select your csv file   Go through the wizard and then in  column data format  select any column that needs to be forced to  text    This will import that entire column as a text format preventing Excel from trying to treat any specific cells as a date

User · Answer

EXCEL 2007 and later   How to force excel not to  detect  date formats without editing the source file  Either    rename the file as  txt If you can t do that  instead of opening the CSV file directly in excel  create a new workbook then go to  Data  gt  Get external data  gt  From Text and select your CSV    Either way  you will be presented with import options  simply select each column containing dates and tell excel to format as  text  not  general

User · Answer

I know this is an old thread  For the ones like me  who still have this problem using Office 2013 via PowerShell COM object can use the opentext method  The problem is that this method has many arguments  that are sometimes mutual exclusive  To resolve this issue you can use the invoke-namedparameter method introduced in this post  An example would be  ex   New-Object -com  quot Excel Application quot   ex visible    true  csv    quot path to your csv csv quot  Invoke-NamedParameter   ex workbooks   quot opentext quot     quot filename quot   csv   quot Semicolon quot    true   Unfortunately I just discovered that this method somehow breaks the CSV parsing when cells contain line breaks  This is supported by CSV but Microsoft s implementation seems to be bugged  Also it did somehow not detect German-specific chars  Giving it the correct culture did not change this behaviour  All files  CSV and script  are saved with utf8 encoding  First I wrote the following code to insert the CSV cell by cell   ex   New-Object -com  quot Excel Application quot   ex visible    true   csv    quot path to your csv csv quot    ex workbooks add     ex activeWorkbook activeSheet Cells NumberFormat    quot   quot    data   import-csv  csv -encoding utf8 -delimiter  quot   quot     row   1    data       obj        col   1     psobject properties Name    if  row -eq1   ex ActiveWorkbook activeSheet Cells item  row  col  Value2        ex ActiveWorkbook activeSheet Cells item  row 1  col  Value2   obj      col       row      But this is extremely slow  which is why I looked for an alternative  Apparently  Excel allows you to set the values of a range of cells with a matrix  So I used the algorithm in this blog to transform the CSV in a multiarray  function csvToExcel  csv  delimiter         a   New-Object -com  quot Excel Application quot        a visible    true            a workbooks add         a activeWorkbook activeSheet Cells NumberFormat    quot   quot        data   import-csv -delimiter  delimiter  csv         array     data  ConvertTo-MultiArray  Value       starta    int  char  a  - 1      if   array GetLength 1  -gt 26              col    char   int  math   Floor  array GetLength 1  26     starta     char    array GetLength 1  26     Starta         else             col    char   array GetLength 1     starta               range    a activeWorkbook activeSheet Range  quot a1  quot   col  quot  quot   array GetLength 0         range value2    array        range Columns AutoFit          range Rows AutoFit          range Cells HorizontalAlignment   -4131       range Cells VerticalAlignment   -4160     function ConvertTo-MultiArray        param            Parameter Mandatory  true  Position 1  ValueFromPipeline  true             PSObject    InputObject             BEGIN             objects                 ref  array    ref  null             Process             objects     InputObject             END             properties    objects 0  psobject properties       name            array Value   New-Object  object       objects Count 1   properties count            i   row and j   column           j   0           properties                   array Value 0  j       tostring                 j                        i   1           objects                    item                    j   0               properties                        if   item      -eq  null                          array value  i  j     quot  quot                                      else                         array value  i  j     item      tostring                                        j                                i                        array            csvToExcel  quot storage stats csv quot   quot   quot   You can use above code as is  it should convert any CSV into Excel  Just change the path to the CSV and the delimiter character at the bottom

User · Answer

SELECT CONCAT      NOW          firstname  lastname  FROM your table INTO OUTFILE  export csv   FIELDS TERMINATED BY      ENCLOSED BY       LINES TERMINATED BY   n

User · Answer

2018 The only proper solution that worked for me  and also without modifying the CSV   Excel 2010   Create new workbook Data  gt  From Text  gt  Select your CSV file In the popup  choose  quot Delimited quot  radio button  then click  quot Next  gt  quot  Delimiters checkboxes  tick only  quot Comma quot  and uncheck the other options  then click  quot Next  gt  quot  In the  quot Data preview quot   scroll to the far right  then hold shift and click on the last column  this will select all columns   Now in the  quot Column data format quot  select the radio button  quot Text quot   then click  quot Finish quot    Excel office365   client version   Create new workbook Data  gt  From Text CSV  gt  Select your CSV file Data type detection  gt  do not detect  Note  Excel office365  web version   as I m writing this  you will not be able to do that

User · Answer

Assuming Excel 2003      When using the Text-to-Columns Wizard has  in Step 3 you can dictate the data type for each of the columns   Click on the column in the preview and change the misbehaving column from  General  to  Text

User · Answer

A workaround using Google Drive  or Numbers if you re on a Mac     Open the data in Excel Set the format of the column with incorrect data to Text  Format   Cells   Number   Text  Load the  csv into Google Drive  and open it with Google Sheets Copy the offending column Paste column into Excel as Text  Edit   Paste Special   Text    Alternatively if you re on a Mac for step 3 you can open the data in Numbers

User · Answer

Here is the simple method we use at work here when generating the csv file in the first place  it does change the values a bit so it is not suitable in all applications   Prepend a space to all values in the csv  This space will get stripped off by excel from numbers such as   1    2 3  and   -2 9e4  but will remain on dates like   01 10 1993  and booleans like   TRUE   stopping them being converted into excel s internal data types   It also stops double quotes being zapped on read in  so a foolproof way of making text in a csv remain unchanged by excel EVEN IF is some text like  3 1415  is to surround it with double quotes AND prepend the whole string with a space  i e   using single quotes to show what you would type     3 1415    Then in excel you always have the original string  except it is surrounded by double quotes and prepended by a space so you need to account for those in any formulas etc

User · Answer

Prefixing space in double quotes resolved the issue    I had data like  7 8  in one of the  csv file columns and MS-Excel was converting it to date as  07-Aug   But with  LibreOffice Calc  there was no issue   To resolve this  I just prefixed space character added space before 7  like   7 8  and it worked for me  This is tested for Excel-2007

User · Answer

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

User · Answer

An alternate method   Convert the format of the column you want to change to  Text   Select all the cells you want to preserve  copy  Without deselecting those columns  click  Edit   Paste Special   As values   Save as CSV  Note that this has to be the last thing you do to the file because when you reopen it  it will format itself as dates since cell formats cannot be saved in CSV files

User · Answer

If you put an inverted comma at the start of the field  it will be interpreted as text   Example  25 12 2008 becomes  25 12 2008  You are also able to select the field type when importing

User · Answer

I made this VBA macro which basically formats the output range as text before pasting the numbers  It works perfectly for me when I want to paste values such as 8 11  23 6  1 3  etc  without Excel interpreting them as dates  Sub PasteAsText     Created by Lars-Erik S  rbotten  2017-09-17 Call CreateSheetBackup      Columns ActiveCell Column  NumberFormat    quot   quot       Dim DataObj As MSForms DataObject Set DataObj   New MSForms DataObject DataObj GetFromClipboard  ActiveCell PasteSpecial  End Sub  I m very interested in knowing if this works for other people as well  I ve been looking for a solution to this problem for a while  but I haven t seen a quick VBA solution to it that didn t include inserting   in front of the input text  This code retains the data in its original form

User · Answer

In Excel 2010 open a new sheet  On the Data ribbon click  Get External Data From Text   Select your CSV file then click  Open   Click  Next   Uncheck  Tab   place a check mark next to  Comma   then click  Next   Click anywhere on the first column  While holding the shift key drag the slider across until you can click in the last column  then release the shift key  Click the  text  radio button then click  Finish   All columns will be imported as text  just as they were in the CSV file

User · Answer

Working off of Jarod s solution and the issue brought up by Jeffiekins  you could modify   May 16  2011    to      May 16  2011

User · Answer

In my case   Sept8  in a csv file generated using R was converted into  8-Sept  by Excel 2013  The problem was solved by using write xlsx2   function in the xlsx package to generate the output file in xlsx format  which can be loaded by Excel without unwanted conversion  So  if you are given a csv file  you can try loading it into R and converting it into xlsx using the write xlsx2   function

User · Answer

This is a only way I know how to accomplish this without messing inside the file itself  As usual with Excel  I learned this by beating my head on the desk for hours   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

User · Answer

Without modifying your csv file you can    Change the excel Format Cells option to  text  Then using the  Text Import Wizard  to define the csv cells  Once imported delete that data then just paste as plain text   excel will properly format and separate your csv cells as text formatted ignoring auto date formats   Kind of a silly work around  but it beats modifying the csv data before importing    Andy Baird and Richard sort of eluded to this method  but missed a couple important steps

User · Answer

While creating the string to be written to my CSV file in C  I had to format it this way           myVariable

User · Answer

Paste table into word  Do a search replace and change all -  dashes  into --  double dash   Copy and paste into Excel  Can do same for other symbols      etc  If need to change back to a dash once in Excel  just format the column to text  then make the change  Hope this helps

User · Answer

Its not the Excel  Windows does recognize the formula  the data as a date and autocorrects  You have to change the Windows settings    Control Panel   -   Switch to Classic View   -   Regional and Language Options  -  tab  Regional Options  -   Customize     -  tab  Numbers  -  And then change the symbols according to what you want   http   www pcreview co uk forums enable-disable-auto-convert-number-date-t3791902 html  It will work on your computer  if these settings are not changed for example on your customers  computer they will see dates instead of data

User · Answer

This issue is still present in Mac Office 2011 and Office 2013  I cannot prevent it happening  It seems such a basic thing   In my case I had values such as  1 - 2   amp   7 - 12  within the CSV enclosed correctly within inverted commas  this automatically converts to a date within excel  if you try subsequently convert it to just plain text you would get a number representation of the date such as 43768  Additionally it reformats large numbers found in barcodes and EAN numbers to 123E  numbers again which cannot be converted back   I have found that Google Drive s Google Sheets doesnt convert the numbers to dates  The barcodes do have commas in them every 3 characters but these are easily removed  It handles CSVs really well especially when dealing with MAC   Windows CSVs   Might save someone sometime

User · Answer

Still an issue in Microsoft Office 2016 release  rather disturbing for those of us working with gene names such as MARC1  MARCH1  SEPT1 etc   The solution I ve found to be the most practical after generating a   csv  file in R  that will then be opened shared with Excel users    Open the CSV file as text  notepad  Copy it  ctrl a  ctrl c   Paste it in a new excel sheet -it will all paste in one column as long text strings  Choose select this column   Go to Data-  Text to columns      on the window opened choose  delimited   next   Check that  comma  is marked  marking it will already show the separation of the data to columns below   next   in this window you can choose the column you want and mark it as text  instead of general   Finish     HTH

User · Answer

I had a similar problem and this is the workaround that helped me without having to edit the csv file contents   If you have the flexibility to name the file something other than   csv   you can name it with a   txt  extension  such as  Myfile txt  or  Myfile csv txt    Then when you open it in Excel  not by drag and drop  but using File- Open or the Most Recently Used files list   Excel will provide you with a  Text Import Wizard    In the first page of the wizard  choose  Delimited  for the file type   In the second page of the wizard choose     as the delimiter and also choose the text qualifier if you have surrounded your values by quotes  In the third page  select every column individually and assign each the type  Text  instead of  General  to stop Excel from messing with your data   Hope this helps you or someone with a similar problem

User · Answer

I have jus this week come across this convention  which seems to be an excellent approach  but I cannot find it referenced anywhere   Is anyone familiar with it   Can you cite a source for it   I have not looked for hours and hours but am hoping someone will recognize this approach   Example 1     012345678905   displays as 012345678905  Example 2     1954-12-12   displays as 1954-12-12  not 12 12 1954

User · Answer

I have found that putting an     before the double quotes will accomplish what you want  It forces the data to be text   eg    2008-10-03    more text   EDIT  according to other posts   because of the Excel 2007 bug noted by Jeffiekins one should use the solution proposed by Andrew      2008-10-03

User · Answer

If someone still looking for answer  the line below worked perfectly for me I entered    quot my value quot    i e     quot 04SEP2009 quot   displayed as 04SEP2009 not as 09 04 2009 The same worked for integers more than 15 digits  They weren t getting trimmed anymore

User · Answer

I do this for credit card numbers which keep converting to scientific notation  I end up importing my  csv into Google Sheets  The import options now allow to disable automatic formatting of numeric values  I set any sensitive columns to Plain Text and download as xlsx   It s a terrible workflow  but at least my values are left the way they should be

User · Answer

WARNING  Excel  07  at least  has a nother  bug  if there s a comma in the contents of a field  it doesn t parse the   field  contents  correctly  but rather puts everything after the comma into the following field  regardless of the quotation marks     The only workaround I ve found that works is to eliminate the   when the field contents include a comma   This may mean that there are some fields that are impossible to represent exactly  right  in Excel  but by now I trust no-one is too surprised

[excel] Stop Excel from automatically converting certain text values to dates

Examples related to excel

Examples related to csv

Examples related to import