Stop Excel from automatically converting certain t

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.

Answer 1

Working off of Jarod's solution and the issue brought up by Jeffiekins, you could modify

"May 16, 2011"

to

"=""May 16, 2011"""

Answer 2

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.

Answer 3

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.

Answer 4

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) & "_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

Answer 5

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"

Answer 6

(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 > Get external data > 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".