Excel auto formats cells with numbers - Can it be disabled?
 

I am having a problem within Excel, all versions.

A piece of software I use daily uses CSV files for inventory management for
Books, CD's and DVD's.

The problem happens when you enter in an ISBN or UPC that is all numbers and
starts with a 0. Excel will see all numbers and automatically format the cell
as a number, thus dropping the leading 0. I know you can put a dash, pipe
character or whatever to stop this. I also know you can right click on cells
and change formatting.

But the problem with both of these are 1: CSV's do not save formatting, so
the 0 will be dropped once the file is opened again. 2: The program I use
will not allow a hyphen or Pipe character in the UPC or ISBN's.

So, essentially, what I need is an option to have Excel to *Never*
automatically format cells depending on their context.

I have gone through and disabled *Every* auto formatting and such rules I
could find in every corner of the options. I have also used the Import text
wizard to stop it and it doesn't work.

Am I right? Is there no way to stop Excel from formatting cells automatically?

Thanks

Excel auto formats cells with numbers - Can it be disabled?
 

Hi Jeromey,
You could add a prefix to the code, then after the CSV has been
brought into Excel go a global change on the column to remove the
prefix.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jeromey" wrote in message ...
I am having a problem within Excel, all versions.

A piece of software I use daily uses CSV files for inventory management for
Books, CD's and DVD's.

The problem happens when you enter in an ISBN or UPC that is all numbers and
starts with a 0. Excel will see all numbers and automatically format the cell
as a number, thus dropping the leading 0. I know you can put a dash, pipe
character or whatever to stop this. I also know you can right click on cells
and change formatting.

But the problem with both of these are 1: CSV's do not save formatting, so
the 0 will be dropped once the file is opened again. 2: The program I use
will not allow a hyphen or Pipe character in the UPC or ISBN's.

So, essentially, what I need is an option to have Excel to *Never*
automatically format cells depending on their context.

I have gone through and disabled *Every* auto formatting and such rules I
could find in every corner of the options. I have also used the Import text
wizard to stop it and it doesn't work.

Am I right? Is there no way to stop Excel from formatting cells automatically?

Thanks