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.

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.

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.

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.

Originally Posted by oldchippy

Which website?

This one for example:
http://www.liverpoolfc.tv/match/season/

You might need to get thru the splash screen and click the [Matches] menu.

Is it really impossible to turn off this function.
It can’t be right. There has to be something I can do to prevent Excel from turning 1-1 into 01-jan when I’m copy/pasting date into Excel.

I get this:

07-jul-07 Wrexham Friendly A 15:00 Y Y Y 03-feb
14-jul-07 Crewe Alexandra Friendly A 15:00 Y Y Y 3-0
17-jul-07 Werder Bremen Friendly A 19:15 Y N Y 03-feb
20-jul-07 Auxerre Friendly A 14:00 Y N Y 2-0
24-jul-07 South China FC Barclays Asia Trophy A 13:30 Y N Y 03-jan
27-jul-07 Portsmouth Barclays Asia Trophy A 13:30 Y N Y 02-apr
03-aug-07 Shanghai Shenhua Port of Rotterdam Tournament A 17:30 Y N Y 2-0
05-aug-07 Feyenoord Port of Rotterdam Tournament A 19:00 Y N Y 01-jan
11-aug-07 Aston Villa Barclays Premier League A 17:15 Y N Y 02-jan
15-aug-07 West Ham United Barclays Premier League Postponed 17:15 Y N Y P-P
15-aug-07 Toulouse Champions League Qualifier (1) A 15:30 Y N Y 1-0
19-aug-07 Chelsea Barclays Premier League H 16:00 Y N Y 01-jan
25-aug-07 Sunderland Barclays Premier League A 12:45 Y N Y 2-0
28-aug-07 Toulouse Champions League Qualifier (2) H 20:05 Y N Y 4-0
01-sep-07 Derby County Barclays Premier League H 15:00 Y N Y 6-0
15-sep-07 Portsmouth Barclays Premier League A 12:45 Y N Y 0-0
18-sep-07 FC Porto Champions League Group Phase A 19:45 Y N Y 01-jan
22-sep-07 Birmingham City Barclays Premier League H 15:00 Y N Y 0-0
25-sep-07 Reading Third round Carling Cup A 19:45 Y N Y 04-feb
29-sep-07 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 02-feb
20 Oct, 2007 Everton Barclays Premier League A 12:45 Y N Y -
.
.
.

The problems continue down the last column for me.

Select an area at least as big as the number of rows & columns required to paste into.

Right click : Format Cells... : Text

Then paste.

If the area is too big EXCEL will warn you, but if you tell it to carry on regardless it will work properly.

Mark.

Hi Mark and oldchippy.

Mark I tried the pre-formatting thing and it doesn’t work. Any text I paste into Excel that can be turned into a date, as described above, will be turned into a date no matte where I copy it from.

Oldchippy the link isn’t broken when Excel turns my date into dates.
So a 1-1 result what has a link in it will be turned into 01-jan and still have the link.
Btw I don’t have the 12th line problem. It’s the same problem for me from the first to the last line.

Ok I have the solution and I thought I would share it with you.

First, I format the area I want to paste my data into as I did in the first place.
Then I pasted the data into Excel. At the bottom right corner there is the "clipboard" where you have the option of "keep the source format" or "Use the same format as the destination" (Translated from Danish).

I chose "Use the same format as the destination" and it changed all the dates back to what I wanted, 1-1, 2-7 etc.

Anyways thanks for the help guys. Have a nice day

/Thomas