Re: Opening tab-delimited files in Excel... how to stop the autoformat of dates?

[[email protected]]

Do a Data-->get external date-->import text files (from the main menu).

Select the .csv file and follow the dialogue. On one screen you can see
the formatted data. On this screen you can set the format for each
column. Select the date column and make it text.

Hans

[Pete_UK]

If it is a file with the extension .txt and you do File | Open within
Excel, it will automatically take you into the Data Import Wizard where
you can do as Hans describes. So, it might be a bit easier if you
rename the .csv file to .txt.

Hope this helps.

Pete

[salamander]

Thanks for the suggestion. Unfortunately, I should have been more clear and pointed out that I was already aware of this workaround. Because of the large volume of tab-delimited files that I deal with (I've written numerous programs that output tab-delimited files that are best viewed in Excel), manually importing each file is far from ideal. I was hoping there was a way for me to simply eliminate the date autoformatting so that I can open these files with a double-click.

Seems hard to believe that Microsoft doesn't enable users to turn this feature off...

Thanks for your time!

[Pete_UK]

Well, you could do it once manually and record a macro while you do it,
setting the appropriate field to text, then in future you would only
need to run the macro (and change the filename) to import the data how
you want it.

Pete

[Harlan Grove]

salamander wrote...
>Thanks for the suggestion. Unfortunately, I should have been more clear
>and pointed out that I was already aware of this workaround. Because of
>the large volume of tab-delimited files that I deal with (I've written
>numerous programs that output tab-delimited files that are best viewed
>in Excel), manually importing each file is far from ideal. I was
>hoping there was a way for me to simply eliminate the date
>autoformatting so that I can open these files with a double-click.
>
>Seems hard to believe that Microsoft doesn't enable users to turn this
>feature off...

Naively, it might seem that Microsoft should provide an option to turn
off Excel's 'helpful' features, but Microsoft is convinced such
features are HELPFUL that their attitude towards this is SHUT UP AND
APPRECIATE THE HELP, YOU STUPID USER.

To the point, if you're the one generating these tab-delimited files,
you could append an HTML nonbreaking space character (decimal character
code 160) just after the last character of each gene token. This would
prevent Excel from treating these tokens as dates, and they'd appear as
you want them to. However, if you're using these files with other
software, that software would need to strip off the nonbreaking spaces.

Another alternative would be using a macro to unconvert these cells
after import. The macro would search through all nonblank cells and for
any cells containing date values, and when found convert them back into
text. Something like

Sub foo()
Dim c As Range
For Each c In Selection
If VarType(c.Value) = vbDate Then
c.NumberFormat = "General"
c.Formula = "=""" & UCase(Format(c.Value, "mmmd")) & """"
c.Copy
c.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next c
End Sub

[salamander]

Thanks for the excellent suggestions!