Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm copy-pasting some data from html table into excel. But excel automatically converts some text or number values to date! When I change the format, the number is perversed, the number is something like 4112523 (excel probably interprets the cell as date and then converts to number or something like that...)

There is a trick for importing CSV files, but is there any solution when you are pasting your data directly from a web browser?

share|improve this question

migrated from stackoverflow.com Nov 24 '12 at 11:06

This question came from our site for professional and enthusiast programmers.

    
Have you tried custom formula? –  Jeremy John Nov 19 '12 at 17:35
    
Here is a similar question stackoverflow.com/q/13082641/1370465 –  Jook Nov 19 '12 at 17:44
    
@JeremyJohn - custom formula? What do you mean? –  Tomas Nov 19 '12 at 17:53
    
but the data you are pasting is not about dates, right? –  BrOSs Nov 19 '12 at 17:54
    
@BrOSs - exactly –  Tomas Nov 19 '12 at 17:55
show 2 more comments

5 Answers

up vote 2 down vote accepted

Try this, it worked out for me:

Data - Import external data - New web query and follow the wizard

It's so simple.

regards!

share|improve this answer
    
thank you, nice tip! But it still converts the fields... –  Tomas Nov 19 '12 at 20:09
    
I have found an import option "Disable Date Recognition" that did the trick!! So this (the import) is the only way that works for me! –  Tomas Nov 19 '12 at 20:21
    
Unfortunatelly, this works only when copy-pasting from a web source. If it is copy pasting from MS Access for example, this is unusable. –  Tomas Nov 19 '12 at 20:25
    
The import external data worked for me too with MS Access, you just look for the .mdb source file. Are you working with office 2007+ ? –  BrOSs Nov 19 '12 at 21:04
    
Yes, but this is complicated - it is much more comfortable to mark the rows you want in Access... I have office 2003 –  Tomas Nov 19 '12 at 23:07
show 1 more comment

options/Transition

transition formula evaluation - enable it

PS

and forget about already pasted and saved data. =(

share|improve this answer
    
thanks, but this option is unchecked! –  Tomas Nov 19 '12 at 20:11
1  
Great! This works! Unfortunatelly, it perverses other data... there is a date in another column in the form "01/05/1998" and it computes 0,0001001 out of it :-) :-( –  Tomas Nov 20 '12 at 7:18
1  
And it should be "transition formula entry" instead.. and note that this option is per sheet.. –  Tomas Nov 20 '12 at 7:21
add comment
  1. Open a Blank Workbook
  2. Click on Data
  3. Click From Text
  4. Locate your CSV/txt file
  5. Click Next
  6. Choose your Delimiter
  7. Click Next
  8. Use the scroll bar to locate the column you wish to protect and click on it
  9. Select Text from the Column data format
  10. Click Finish
  11. Click OK

The data will now have been imported without the date conversion.

share|improve this answer
    
interesting solution, thanks! –  Tomas Oct 2 '13 at 18:25
add comment

Try following options :

1) Select the cells and go to Format --> cells --> number and select Text for the selection

OR

2) While pasting it from MS word paste it using Paste Special option and select 'Values only" option in the dialog box

share|improve this answer
    
1) doesn't work - the number is already perversed when I change format to text. 2) doesn't work - Paste Special does not contain the standard offer, but only: HTML, text in Unicode, text. Because I'm pasting html content. –  Tomas Nov 19 '12 at 17:57
add comment

This is a bit of a kluge, but if the data can be pasted normally (without using Paste Special) it usually works:

  1. Format all empty cells where dates will fall as text.
  2. Right-click when pasting and select "Match destination formatting."

Be careful, though, b/c if you paste numbers into the cells that you've pre-formatted, they will also be converted to text, and you'll have to use value() or such to get them back (with Excel 2013 you get a preview of where the data will go just be hovering over the Paste button, so I usually do that and then convert the columns where date-like strings will land first).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.