Auto Format option in excel

 Team

I am having problem in Excel when it do the auto format.

I have a text file which has the following data which are tab separated value

Name                     Date                  Amount1                    Portion

XXX                        12-5-2008         11.25                         10/25

When i load this text file into excel.(By changing the extension from .txt to .xls)

It convert 11.25 to 25-Nov who ever is opening this excel in Europe Region and 11/25 is converting to 25-Oct who ever is opening this excel from North America.

I don't want excel to interpret my data. I want to suppress the auto format.

How can I do this? Any one please helps me out to resolve this.

Thanks & Regards

Subhashini

 Subhashini

You do not need to change the extension. Just go to Open and it will launch the text import wizard. Mark those columns as text in the wizard (step 3) and then if you need to use these as number (do calculations on them), then just copy a cell with a 1 in it and go to the home tab and paste special and select Values+Multiply. This will change them back to numbers which can be formatted as you like.

If that has to be done many times, record yourself doing it with the macro recorder. it's ideal for this type of operation.

Hi  Nick Hodge

No the problem is i will pull the data from database and put it as a tab seperated value to the request object. and the content type will be excel.

response.setHeader(

"Content-Disposition","attachment; filename=SummaryReport.xls");

so it will automatically open in excel when they click on excel export button.

Due to the auto format Europe will get 25-Oct

 Subhakarthi

I'm tempted to say nothing has changed between 2003 and 2007 in this regard, but you are maybe seeing different. There are a few alternatives.

Prefix the data from the database with a single apostrophe, will not show in Excel but will treat them as text. Try allowing the users to hit the database directly with a query. (Not sure if that route will make any difference though). If you set the data in a table on an html page, they could do a web query and there is a setting under options in the set up dialog to stop the conversion of dates.

The only other thing I can think of is to try some other mime types and see if that stops it.

It is an age old problem and there is little that can be done in some instances, but in defence, for some people the shortcut when typing their data in sheets to convert is desirable. Damned if you do...damned if you don't, that kind of thing.

I agree it would be nice to be able to 'type a column' or for Excel to recognise text data from databases, but it really is a numeric spreadsheet I guess, not a database???