Stop text to date conversion

[Ethan Strauss]

Hi,
I would like to stop Excel (I use Excel 2007) from converting text to
dates by default. I know various ways to do this in specific instances, but I
want to stop it in ALL cases. Ideally I would like a method I can show/give
to other people. I would be happy to write an add-in (probably C#.net using
VSTO) which does it.
The problem is that I have lists of Gene names which include values like
"Oct4", "Sep5" and so forth. When these lists are imported from .csv files,
pasted, or otherwise put into Excel, some names morph into date values. It is
generally not obvious when 3 out of 1000 values have morphed and it does not
get caught until it is very difficult to correct. I know that you can fix it
at the time of import (make sure the column is Text format, put a ' in front
of the value etc), but most people don't know this. If I could come up with a
general fix, that would be great! For some more info on the problem, see
http://www.biomedcentral.com/1471-2105/5/80.
Thanks!
Ethan

 

[Rich Locus]

Ethan:

I have had similar problems where I import a spreadsheet and it morphs.
What I do is this:

1)Import the data from CSV.
2) Select the Column you want to turn to text
3) Copy the value from the column into a String variable
4) Immediately copy the value back to the cell (it adds the leading
apostrophe)
5) Loop through all the rows

Here'a an snippet where I convert imported morphed numbers to text. Note
that variables for the counter are LONG, and the strStyle is string.

' ********************************************************
' Format Imported Numbers As Text (should work for Dates)
' ********************************************************
Columns("A:A").Select
Selection.NumberFormat = "@"
Range("A1").Select

For intConvertToText = 2 To intNumberOfRowsInWorksheet
strStyle = Cells(intConvertToText, 1).Value
Cells(intConvertToText, 1).Value = strStyle
Next intConvertToText