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

S

salamander

Hello,

I'm doing bioinformatics work in which I often download tab-delimited
files describing genomes and open them in Excel. Unfortunately, Excel
decides that genes with names like APR1 and SEP7 are actually dates,
and reformats them as such. This is a huge headache, as I often don't
notice these problems until much later.

Is there anyway to completely disable this date autoformatting? I've
searched high and low and have yet to find a solution.

Thanks!

P.S. I'm using Excel 2002
 
H

hansyt

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
 
P

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
 
S

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!
 
P

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
 
H

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...
Click to expand...

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
 
Top