How To Get Excel To Stop Auto-Formatting my Column Data Types

[kmccrack]

Hi:

I'm using Excel 2003. Anyone know how to get it to stop automatically
choosing a data type for my columns? Some of its automatic features
are nice but its auto data type sometimes gets in the way. Example, I
run a program which exports data to Excel. The data contains fractions
such as 5/6 and 6/6. When I open the newly-created Excel file, Excel
converts those fractions to dates such as May 6 and June 6. If I could
get to the worksheet ahead of time, I would format the column as text.
But it's too late by the time I look at the worksheet and converting to
text after Excel converts my fraction to a date results in just the
serial date entry.

Anyone know how to shut off the auto column data type feature?

Thanks!
Kevin

 

[Nick Hodge]

Kevin

You can't realy switch it off. You are right about pre-formatting, but that
obviously doesn't work for you. You could put a single apostrophe (') in
front of the data before importing. If this import is a csv file format,
try taking off the extension and then opening via file open in Excel.

This will invoke the text import wizard and you can select to mark the
column as data in the wizard. If this is repetitive, you can record a macro
to run it each time

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Harlan Grove]

Nick Hodge wrote...

You can't realy switch it off. You are right about pre-formatting, but that
obviously doesn't work for you. You could put a single apostrophe (') in
front of the data before importing. If this import is a csv file format,
try taking off the extension and then opening via file open in Excel.

This will invoke the text import wizard and you can select to mark the
column as data in the wizard. If this is repetitive, you can record a macro
to run it each time

....

For CSV files, another alternative would be using Data > Import
External Data > Import Data, which will launch the text import wizard
just after the user selects a CSV file. However, this would import the
file *into* the active workbook rather than opening the CSV file as a
separate workbook.

 

[Nick Hodge]

Harlan

It amazing really, I use that feature every day, but never consider it for
text/csv files etc, although many times data in the same workbook is
desireable. I only use that for 'real' databases, Access (Said quitely in
case Aaron comes back), SQL server and iSeries Client Access...strange, must
try harder

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[k-man]

Nick Hodge wrote...




...

For CSV files, another alternative would be using Data > Import
External Data > Import Data, which will launch the text import wizard just
after the user selects a CSV file. However, this would import the file
*into* the active workbook rather than opening the CSV file as a separate
workbook.

Ahhhhh, the Import Wizard!!! Yes, that's it! Some control at last.
Like with Nick, I use the Wizard for stuff like Access. I guess since
Excel just automatically opened CSV files I never considered that I
would be able to load them manually. Nice.

Thanks Harlan and Nick!

Kevin