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?

  • Have you tried custom formula? Nov 19, 2012 at 17:35
  • Here is a similar question stackoverflow.com/q/13082641/1370465
    – Jook
    Nov 19, 2012 at 17:44
  • @JeremyJohn - custom formula? What do you mean?
    – Tomas
    Nov 19, 2012 at 17:53
  • 12
    The fact that any software thinks the most appropriate way to behave is to assume you want it to reformat whatever information you gave it because surely you didn't mean for it to be THAT way... with no easy and obvious way to disable it... is the single dumbest design decision and annoying thing about Excel. I should not have click through an import wizard if all I am trying to do is copy/paste from one CSV to another. The friggin thing is a number in one CSV. Why on God's green earth would I want it to paste a date? What idiot designed that?
    – Matt Welch
    Aug 25, 2014 at 14:55
  • 3
    Before pasting, format the whole sheet as text. Now, the data you paste will not be reformatted or even analyzed for you in any way!
    – DanO
    Oct 22, 2014 at 22:00

11 Answers 11


Try this, it worked out for me:

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

It's so simple.


  • thank you, nice tip! But it still converts the fields...
    – Tomas
    Nov 19, 2012 at 20:09
  • 2
    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, 2012 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, 2012 at 20:25
  • 1
    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, 2012 at 21:04
  • 10
    Why is this the accepted answer? It doesn't address copy/pasting at all.
    – DanO
    Oct 22, 2014 at 22:01

This is definately a kluge and it's the only thing I've figured out to do. I came here looking for a more elegant solution.

I do have a slight improvement for this.

  1. Paste the data into the spreadsheet as is.
  2. Select all and format as text
  3. Paste again over what you did the first time but this time as values.

This keeps you from having to count columns and rows, etc.

If you have data you actually want to be numbers, dates, etc, it's much easier to reformat them correctly from this end than it is to do from the other. Since the numbers won't automatically convert, I used text to columns on the column, selecting "delimited" but unchecking all the delimiters. This has the same effect as going into each cell and hitting enter to get Excel to recognize it again. Ugly, but it works.

  • 8
    The real trick is that the cells must be formatted to text beforehand. The first paste helps identifying which cells are going to be covered afterwards. Good advice!
    – user146539
    Sep 7, 2014 at 22:32
  • Caution: Padded numbers, such as "00001", lose their padding. These are converted to 1 and than to "1". The original number is unrecoverable. Tibo's solution works nicely. Nov 23, 2017 at 2:46
  • MS advices the same: support.office.com/en-us/article/…
    – Alekzander
    Oct 10, 2018 at 16:55
  • Note: Does not work from Word. Does work from Notepad. Mar 4, 2019 at 15:27

The Most simple way.

1. Copy the original Data
2. Paste to Notepad (Prefer Notepad++)
3. Change the Cell Properties to TEXT
4. Copy All from Notepad
5. Paste back to Excel.
  • OK, so for million cells you do it million times?
    – Tomas
    Sep 12, 2014 at 14:04
  • U can select the whole column from the top. It will select all, and copy and paste to the notepad.
    – Shiro
    Sep 13, 2014 at 3:52
  • 1
    This works! you can even just start at step 3! Select the whole sheet, format as text, paste! done.
    – DanO
    Oct 22, 2014 at 21:48
  • This is what I end up doing because just changing the data to text within Excel doesn’t always work, e.g. if you have some ‘text’ that happens to be a long number (product codes etc.), Excel displays it in scientific notation.
    – user535673
    Jun 10, 2016 at 9:43
  • Excel is stupid. WHY DO THEY DO THIS TO US??? However, your solution is the only one that consistently works. Aug 3, 2017 at 5:18

Before pasting, select column that will hold the non-date value. Right click. Select 'format cells'. Then select 'text' as the format and click OK.

Now, go ahead and paste, but paste using the 'match the destination formatting' option.

  • This does not work for me. Aug 3, 2017 at 5:29
  • For formatting, "Text" works and "General" doesn't work. The detail I missed. Thanks Paul.
    – HongPong
    Apr 4, 2018 at 21:03
  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.


Select all the cells, and change their format to Text.

Then choose Paste -> Match Destination Formatting.



transition formula evaluation - enable it


and forget about already pasted and saved data. =(

  • 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, 2012 at 7:18
  • 1
    And it should be "transition formula entry" instead.. and note that this option is per sheet..
    – Tomas
    Nov 20, 2012 at 7:21

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).


Try following options :

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


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

  • 1
    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, 2012 at 17:57
  1. In your internet browser, File/Save Page as/Save As-Webpage, Complete/"name it"
  2. Open Excel (2007), File/Open/"name it" File you saved in #1

Extension of Alex Robilotto's Answer for clipboard object not formatted as "text".

  • E.g., this works for web-based clipboard objects

The steps:

1. Paste the data into the spreadsheet as is.
2. Select all and format the cells as text
3. Paste original clipboard object again over what you did the first time
   - But this time select "Paste Special..." and select "Text".

Note: pasting as value is not an option for non-text, web-based clipboard object.

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