automatic formatting in excel

We use account codes beginning with MAR with 4 digits following (e.g.
MAR3420) in spreadsheets and Excel automatically converts this to a date
format.

I have reformatted the cells to Text and this overcomes the problem initially.

However, these files have to be saved in .csv format for uploading into our
accounting software. The difficulty arise if the .csv file is ever opened to
make changes because Excel reformats these cells to dates again which
corrupts our data.

Does anyone know of a way to switch off the automatic reformatting of cells?

I don't think you can prevent Excel from being overly helpful, but there
are some other trick that might help. An alternative to preformatting
as text is to precede the account code with a single quote; that tells
Excel that what follows is text, and not to be interpreted as a date or
scientific notation. Unfortunately, Excel's .csv interpreter is not
very bright; it completely ignores (double) quotation marks around text,
and still coerces numbers or dates if possible, also it will show the
leading single quote, unlike keyboard entry in Excel. The formula
="MAR3120"
in a .csv file will be respected by Excel, but may not be accepted or
returned by your accounting program.

Perhaps the easiest way to return the data from you accounting program
use Data|Import External Data instead of File|Open. That will trigger
the Text Import Wizard which allows you to specify that account code
column is to be interpreted as text.

Jerry

Phil Hanley wrote:

We use account codes beginning with MAR with 4 digits following (e.g.
MAR3420) in spreadsheets and Excel automatically converts this to a date
format.

I have reformatted the cells to Text and this overcomes the problem initially.

However, these files have to be saved in .csv format for uploading into our
accounting software. The difficulty arise if the .csv file is ever opened to
make changes because Excel reformats these cells to dates again which
corrupts our data.

Does anyone know of a way to switch off the automatic reformatting of cells?