Excel auto format changing 1-1 into date

First of all I use Office 2003.

When I copy/paste some data into Excel it turns everything that CAN be turned into a date into something like 01-jan when it should say 1-1.

1-1
4-5
8-3
18-12
Turns into:
01-jan
04-may
08-mar
18-dec

I can’t pre format the cells to be text. It’s still turns it into the date.

Isn’t there a function I can turn off so it doesn’t turn my data into dates?

Originally Posted by oldchippy

Try this, select the area/column you wish to paste into, right click and format the area/columns to TEXT, then go to your data, copy > paste special > TEXT

The result is all the data is pasted into the right rows but everything is in column A instead of A to I.

What I want is to turn off this stupid function. I’m never going to use dates in Excel.

Originally Posted by oldchippy

Ok, with everything in column A, then go to Data > Text to columns and follow the steps through

This is just an example of data I copied from the net:

15 Sep, 2007 Portsmouth Barclays Premier League A 12:45 Y N Y 0-0
18 Sep, 2007 FC Porto Champions League Group Phase A 19:45 Y N Y 1-1
22 Sep, 2007 Birmingham City Barclays Premier League H 15:00 Y N Y 0-0
25 Sep, 2007 Reading Third round Carling Cup A 19:45 Y N Y 4-2
29 Sep, 2007 Wigan Athletic Barclays Premier League A 15:00 Y N Y 1-0
03 Oct, 2007 Olympique Marseille Champions League Group Phase H 19:45 Y N Y 0-1
07 Oct, 2007 Tottenham Hotspur Barclays Premier League H 15:00 Y N Y 2-2

Everything is as described above in column A. As far as I know, there isn’t any way to place the date the right way if it looks like it does after inserting it like you said in your first post.

It’s on the right cells if I paste it directly from the homepage, but the date thing is there.

There HAS to be a way to disable the date auto format thing

Anyways thanks for helping oldchippy.

This solution will depend on where your original data is coming from.

If your data is coming from a source that allows you to fairly easily alter it then if you could change your 1-1 to '1-1 this should solve your problem.

Mark.

Another option might be to save your data into a text file then open this text file into EXCEL.
The wizard that comes up for loading text files into EXCEL is pretty good and allows each column to be individually specified as general, number, text, date etc...

Standard cut and paste always seems to assume general.

Mark.

Originally Posted by Mark@Work

This solution will depend on where your original data is coming from.

If your data is coming from a source that allows you to fairly easily alter it then if you could change your 1-1 to '1-1 this should solve your problem.

Mark.

I want to copy/paste it directly into Excel without pre-formatting the 1-1 with an ' in front. I did that before and tbh it’s getting too much.
Previously I used Word to get the ' in front of my data, but its getting too complicated.

It’s a very simple and in my case very annoying function you should be able to turn off.