Disable date formatting when copy paste into excel
You may have seen the following situation in excel: there is some table in HTML or even notepad/word and you want to copy paste into excel,
If you do the simply copy
and Paste Special (html) in excel,
All the data is ok except for some values which contains the results (e.g. 0-0,1-0,2-1,1-3 etc)
So this 1-3 will appear as 1-Mar, 2-1 will appear as 1-Feb in excel etc.
It's very annoying and you really don't want excel to be that smart to do the formatting, even if you highlight those values and then select "format cells" as "text", it's not working.
There are 2 ways we can get this solved:
1) Don't try to copy directly from the html, instead, choose "Data--> From web
--> Options --> Check: Disable Date recognition",
then the table you will see in excel is the one without that auto-formatting, you might need to delete some other contents since it's directly loading from the whole HTML.
2) The easiest way is the following: you still copy the table from html, then in the excel,
before you do the paste, try to select a few columns and select "format cells" as "text",
then you do the paste, you will get exactly what you want!
The tricky part is "format cells" as "text" before the paste!
3) Some people mentioned the following way, but it seems not working:
Tools --> Options --> Edit and uncheck "Extend Data Range Formats and Formulas", it seems not working!
Highly Persuasive Presentation Secrets--Part III
Back to Highly Persuasive Presentation Secrets--Part V
Interview Question and Answers