How2prevent excel from auto formating n auto aplying formula 2a cell when opening CSV

[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 --> [email protected]

thanks for your time

 

[FDDavis]

When you import the data to excel from the csv file using the impor
wizard assuming you use a "," as the delimiter charracter selec
delimit then next then select the comma check box then next then o
this screen you can set all columns to text or selected columns as yo
wish also here you can adjust your columns devider. select finishe
then ok and that should get you there

Hope this help

 

[Bernie Deitrick]

Mike,

Rename your file to have a .txt or .prn extension. Excel will start its text
import wizard when you try to open it, and you can select and format columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MVP

 

[MikeVince]

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you can
just double click the file from windows explorer then excel will
automatically opens it. coz with .txt, i need to open excel first and
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell as
3-1-2004 instead of 3/1. is there any way to tell excel to leave the
cell as exactly as it is in the original file without applying any
formula or formatting?


regards,

 

[MikeVince]

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you ca
just double click the file from windows explorer then excel wil
automatically opens it. coz with .txt, i need to open excel first an
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell a
3-1-2004 instead of 3/1. is there any way to tell excel to leave th
cell as exactly as it is in the original file without applying an
formula or formatting?


regards,

Mike,

Rename your file to have a .txt or .prn extension. Excel will star
its text
import wizard when you try to open it, and you can select and forma
columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MV

 

[MikeVince]

Hi,

how to invoke the import wizard when opening .csv file?
when i open csv file, excel wont invoke the import wizard unlike when
open a .txt file.


regards,

 

[Dave Peterson]

Not that I know.

But if the file you're importing is always the same layout, you could rename the
file to *.txt and then record a macro when you import it.

Then clean up that macro code a bit, put a button on a worksheet, assign your
macro to the button and distribute workbook with the macro.

It might be even easier for the end user. You can build formatting, subtotals,
page setup and more into your macro.

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you can
just double click the file from windows explorer then excel will
automatically opens it. coz with .txt, i need to open excel first and
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell as
3-1-2004 instead of 3/1. is there any way to tell excel to leave the
cell as exactly as it is in the original file without applying any
formula or formatting?

regards,

Mike,

Rename your file to have a .txt or .prn extension. Excel will start
its text
import wizard when you try to open it, and you can select and format
columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MVP