macro to prevent Excel changing 1-2-3 to a date

[nikki115]

hi,
somebody please help me out!!!!!!

i m trying to paste a lot of data from a web site.and it has a colume where the values r like that;

11 - 96 through
05 - 02

when i paste it into excel it automaticaly changes into date format.but i want to paste it as it is.

i have tried every option like preformating as text ,
creat web query doest work when i do that it says error sorry an error accured in sharkey.
its a lot of data i cant use ' or * with text to start with .

if any body knows the better option please let me know i realy need help to do it .
if any body knows any macro for this problem please reply me with detail how to make it.
thanks

[Tristan]

Hi there,

I tried copy and pasting the examples you had in your message.
The line "11-96 through" copis like it is. Because it has text in it Excel treats as text. the next line however "05-02" contains only numbers and Excel changes it to date format. For some poeple I'm sure that's very helpful but like you it doesn't suit my needs at all and is very frustrating.

The way I get round it is like you tried, preformatting the cells to Text format. However what you also need to do is paste your web content as text, not HTML which is default.

So
1) Preformat your Excel sheet to TEXT
2) Select your content from the web and copy
3) Go to Edit, then Paste Special and select TEXT (or unicode TEXT, I don't what the differece is) instead of HTML.
4) Admire your Excel sheet with the numbers entered as intended.
5) Go to pub and celebrate

Hope this woks for you.
Tris

[phattony72]

Just right click the cell, format cells, choose Text instead of Date

[Tristan]

I've tried what Phattony suggests but it doesn't work for me. It converts the date to the interanl date number (somewhere in the 10,000s range) instead of back to the 10-05 like format you're after. Maybe if something is set differently in your setings it won't do this.
I still suggest sticking to my first reply...especially as there's alcohol involved