Data being corrupted by excels automatic date recognition re-forma

[PaddyD.]

I often cut and paste information from web pages into excel so that I can
manipulate that data. However if the data has numbers seperated by "-" then
it is automatically identified as a date and converted into the excel date
format. This corrupts the underlying data.

Eg. a cell on the web reads "5-22". This gets automatically converted to
1/5/1922 when pasted into a cell (pre-formated as text)l and the format
automatically changed to "custom" "mmm-yy" and displayed as "May-22". By this
stage it's too late to manually convert it back to text as the underlying
value has been changed into a date format of "44682".

Can anyone suggest a solution to this for me...
Please note the following...
I have no control over the starting data so cannot insert characters into it.
Some of the fields within the data are text including spaces, "-", ".", etc

Thanks in advance PaddyD.

 

[T. Valko]

when pasted into a cell (pre-formated as text)

OK, pre-format the destination cells *and* instead of doing a paste do a
paste special>text.

However!!!

If the data being copy/pasted is numeric then it will also get pasted as
TEXT but converting text numbers to numeric numbers is a lot easier than
dealing with data converted to dates!

 

[BSc Chem Eng Rick]

Paddy,
Try changing your "Short date format" to "M/d". To do this:
1. Control Panel
2. If Category View --> Date, Time, Language and Regional Options
--> Regional and Language Options
If Classic View --> Regional and Language Options
3. Regional Options Tab --> Customize...
4. Date Tab --> Short date formate: --> M/d
(please note, M/d is not a standard option so you will have to type
it in but you will see that the "Short date sample:" changes to
reflect what you have asked for)
5. Apply

Now when you paste 5-22 into Excel it will recognise a date Excel will
recognise it as 22 May 2009 (it uses the current year when no year is
specified). You can manually change the year in the formula bar.

Be sure that once you are finished you change the date format back to
something with which you are familiar.

If this helps, please click "Yes"
<><><><><><><><><><><>

 

[Dave Peterson]

If you're only copying a few pieces of data, you could copy and paste into the
formulabar (format the cells as text first). You'll have to do each cell
individually.

If you have lots, you may want to paste into a text file (*.txt), then use
File|Open (or data|Import external data|Import data (in xl2003 menus)).

You'll be prompted with a text to columns wizard and you'll be able to specify
how each field should be treated.

Then (if you used File|Open), you can copy|Paste to its final location.

 

[Gord Dibben]

Instead of cutting and pasting you could Data>Import External Data>New Web
Query.

Enter the URL and go to the webpage.

Select the data by clicking on the yellow arrow(s).

In Options, diasble date recognition.

Import.


Gord Dibben MS Excel MVP

 

[PaddyD.]

OK, pre-format the destination cells *and* instead of doing a paste do a
paste special>text.

However!!!

If the data being copy/pasted is numeric then it will also get pasted as
TEXT but converting text numbers to numeric numbers is a lot easier than
dealing with data converted to dates!

Hi Biff, thanks your reply.
The problem is i'm pasting in tables and if i past in using Paste
Special>Text it only goes into one column. I can't then split this as some of
the table fields are open text format and I can't specify a meaningful
delimiter...

Paddy.

 

[T. Valko]

Ok, well, try one of the other methods in the other replies.