Prevent excel to auto format and auto apply formula to a cell when opening a file

[MikeVince]

Hi,

I have this problem of mine that troubled me for days.

You see, i have a CSV file which has values like :

"3/1","4/2","10-20-2004"

when i open the CSV file using excel, excel will automatically conver
the values to be like:

3-1-2004 4-1-2004 10-20-2004

the 10-20-2004 is okay since it is really a date but the 3/1 and 4/
are not dates and are not any form of equation.

i just want excel to display them as exactly as seen in the CSV file.
so i tried to right click on the rows for both 3/1 and 4/2 and selecte
"Format Cells". Inside the "Format Cells", i selected the "text
category in the "Number" tab. But when i do that, the value for 3/
will become 38047.
I also tried Edit->Clear->All but still it didnt helped.

do you guys know how to make excel display the CSV value of 3/1 a
exactly 3/1 ?

please reply to my email if you know --> (e-mail address removed)

thanks for your time

 

[Peter AStherton]

Mike

Use this formula to convert the date back to text.

=DAY(A1)&"/"&MONTH(A1)

and copy it across. You can use Edit, PAsteSpecial to
paste the numbers back over the original.

Saving it again.

You will have the same problem opening again if it is
saved in CSV format.

Save it in Txt format anad use this macro in another
workbook to open the file (you will have to change the
File name)

Sub Macro1()

Workbooks.OpenText Filename:="C:\My
Documents\Workbooks&Txt\testcsv.txt", _
Origin:=xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 3))
End Sub

This was recorded using Data, Text to columns.

Regards
Peter

 

[Dave Peterson]

Another option:

Rename your .csv file to .txt

file|open
You'll get the wizard and you can specify text for those fields and date (mdy)
for the last.