Every time I copy-paste some numerical data written as fractions or separated by hyhens into Excel, it always converts numbers that could possibly be dates into dates. There's no way of getting them back to the original formatting (as copied). Even if you go to text and back to number, you get a date-number!

Even saving to CSV and importing doesn't fix it!

I've been looking for a solution to this for several years and am still as drustrated as ever.


Anything like 5-6 or 5/2 turns into a date or a datevalue and there is no way to restore the original content of the string or tell Excel you want to treat it as text not a date.

It's not just copy-pasting. Sending data directly from applications through integration or going via CSV doesn't work either. It overrules you on import and changes the content to date values.

"You zero-rep moron, this is solved!"

Before you say this has been asked before. let's have a look at the top questions asking the same thing and the "solutions" posted:

1. Stop excel from converting copy-pasted number/text values to date

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

Doesn't work. And wouldn't be a solution anyway as isn't copy-pasting from a variety of sources and is also incredibly slow.

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

21 upvotes! Really?! Trivially doesn't work with any data as per my example. Also, takes ages.

  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.

13 upvotes. Very slow and kludgey workaround. Doesn't work anyway.

2. How to stop numbers converting to date format on copy paste in Excel?

Select the empty fields (or the entire column), set them to a string format instead of the default value, and do a special paste (available on right-click) with "values only" to prevent a date format from being auto-applies...

I use Paste Special and Paste Values all the time. There is no values option when copy-pasting from, e.g. HTML table. Doesn't work.

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.

Doesn't work. No "match destination formatting" option.

There are few ways to resolve that, the most common and simple one would be to format the cells prior pasting the data as text (select range, right click, format cells, double click on text).

This one again! Doesn't work.

What I have done was to simply change the decimal separator:

Not an option. "-" and "/" are not decimal separators. Doesn't work.

try to add this sing (') in you number in source data then past it into excel the replace (') with same (') because when you add this sing it will be shown in your data in excel but when replace it with it will be hidden.

-1 votes for a reason. Doesn't work.

3. How to prevent numbers to get converted to dates in Excel?

I use Notepad++ for this task: copy your data to notepad++ replace the "." with "," and copy it to excel.

Slow workaround and doesn't help as I need the data to stay as it is. Doesn't work.

First mark the cells that you're going to paste to. Right click, "Format" and then select "Number".

Now right click to paste, and select "Match Destination Formatting"

...really? REALLY?!

Yeah, this is a real pain sometimes - my quick solution is to use cells formatted as text.

Here is how it works:

copy your numbers with the . select a column or some cells change cell-format to text now paste-special (or paste-contents) as unformatted/plain text now replace . with , now change format back to number or default You can put this in some macro of course.

However, I think there might - or at least should be - a better solution.

But as a quickie, this works too ;)

Not quick. And, guess what? Doesn't work.

Answers to this question

After posting this question, I got the following answer from a 16k rep poster:

Please stop complaining about what your version of Excel SHOULD do. Excel 2007 is almost 15 years old, considering it took 3 years to plan its features, develop and test them in the old 3-year release cycles. If you complain about that, you might as well complain that your rotary phone cannot receive text messages. As others have mentioned, modern versions of Excel can do what you want to achieve with less hassle than you have to suffer. So, how about upgrading?

If there's a bug as bad as this, I don't see an issue with asking about it. If there's no solution, fine, but it doesn't mean there's not a way I haven't thought of nor that it's acting as designed.

Anyway, I did upgrade as requested (to Excel 2011) and tried all the suggested answers in this thread. Still no dice. Doesn't work. I don't know whether this poster is able to suggest a combination of OS and Excel version which has been shown to work correctly?

What does Microsoft say?

It simply repeats the well-known (yet completely useless) tricks of prepending with an apostrophe or a 0 to keep formatting or use fraction formatting. Pre-formatting the cells as text might work for manually inputting data, but doesn't when copy-pasting.

A plea

I'm begging you. Please tell me there is a way to wrangle my data into a spreadsheet without changing it!

Even when Excel guesses the format of data and imposes this upon you without asking (why there's no paste as [format] option I don't understand), copy-pasting text or values correctly retains the table structure for every other conceivable text string apart from numbers separated by symbols such as "/" and "-".

Copy-pasting strings in other formats such as 10%, $50.63, 4.25E+11 results in automatic formatting but no loss of the 2-D structure of the data nor does it alter the values/text itself. Only with dates does Excel actually destroy the content of the data by turning text strings into date values. My $50.63 in cell C7 remains $50.63 in cell C7 but 7/8 changes to 43319, 5-4 into 43195 and 6.4 into 43196. This is a one-way function - there is no way to reverse the transformation, the context and content is permanently lost because Excel second-guesses you and doesn't seem to provide a way to tell it you know what you're doing despite all the upvoted "solutions" on this site.


edit: not duplicate as I already covered in the question under heading 2!

  • 1
    Please share what version of Excel you are using and post a data sample for some testing. What are you copying FROM? The web? A text file? Something else, where formatting matters? Please provide these details.
    – teylyn
    Aug 15, 2018 at 21:15
  • This is Excel 2007 on Windows 7 but I have tried several versions over the year never with any joy. HTML examples: link, link. The results: link. I'm copying from the web, word documents, tables output by OCR software. Even with software that links directly into Excel, it will change the format from how it was copied and how it appears and how the cells are formatted to dates losing the meaning and value of the data. The content matters; changing to dates destroys it. Aug 15, 2018 at 21:49
  • 1
    On my Excel 2013 if I first set the format of a range of cells in a worksheet as Text and then Paste Special Text on to it (copied from say wordpad or notepad) then 9/5 is retained as 9/5 instead of being converted to date.
    – patkim
    Aug 15, 2018 at 22:13
  • I know that you stated above that formatting it as text first doesn't work, but I just tried this and it worked for me. It's true that I didn't get all of the paste options, but I did get "Match Destination Formatting" which worked. Caveat on this: I used Word to create a web page table, saved it as HTML, and then opened up with IE. Also, I'm using Office 2016.
    – Rey Juna
    Aug 15, 2018 at 23:24
  • 2
    At least in Excel 2016, if I copy the table from your "JSFiddle" link; format the destination cells in Excel as Text, and then select the Paste option, Match destination formatting appears and allows the table to be pasted as it appears in the original table. No need for Notepad. I don't have Excel 2007 for testing Aug 16, 2018 at 18:42

5 Answers 5


Here are three ways that meet the requirements and a 4th way that's more situational. Yes, 2 of them you said not to work by OP. They work for me on Office 365 Pro Plus so they will work for many others, your mileage may vary depending on Excel version:

Method 1 - Copy and Paste

  1. Format the target range as Text
  2. Copy the HTML data
  3. Paste using Match Destination

Method 2 - Get Data From Web

  1. Click "Get Data" on the Data tab of the ribbon
  2. Hover over "From Other Sources" to expand the drop down menu
  3. Select "From Web" and enter the URL/file location
  4. Select the source table from the list on the left side of the query editor
  5. Click "Edit" on the bottom right
  6. Change every column's data type to Text in the "Transform" group of the Home tab on the ribbon
  7. Click "Close & Load", a table will be created in a blank worksheet

Method 3 - VBA

  1. Paste the code from below into a module
  2. Change PathOrURL to your file location or URL
  3. Run the procedure

I wrote this with an assumption that your data source is the first, or maybe only, table in the HTML file. It converts the source table values to strings and writes the string to the worksheet without using copy or paste. Cell formats don't need to change.

Requires MS Internet Controls and MS HTML Object Library references

Option Explicit
Sub GetTextFromHTML()
    Dim PathOrURL as String: PathOrURL = Environ("USERPROFILE") & "\Desktop\a4rZKDWK.html"
    Dim IE As New InternetExplorerMedium
    Dim x As Long, y As Long
    Dim tbl As HTMLTable
    Dim sVals() As String
    IE.Navigate PathOrURL
    Do While IE.ReadyState <> READYSTATE_COMPLETE
    Set tbl = IE.Document.getElementsByTagName("table")(0)
    With tbl
    ReDim sVals(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
        For y = 1 To .Rows.Length
            For x = 1 To .Rows(1).Cells.Length
                    sVals(y, x) = CStr(tbl.Rows(y - 1).Cells(x - 1).innerText)
    End With
    ActiveSheet.Cells(1, 1).Resize(UBound(sVals), UBound(sVals, 2)).Value = sVals
End Sub

Method 4 - Transitional entry (situational)

  1. Click "File" on the ribbon
  2. Select "Options"
  3. Select "Advanced"
  4. Scroll all the way to the bottom
  5. Check the checkbox for 'transitional entry"
  6. Copy HTML data
  7. Paste in an Excel worksheet

A case can be made for using "transition entry" because it solves the date problem by allowing fractions to be copied and pasted as fractions - HOWEVER - it doesn't satisfy OP's requirements because it also simplifies any reducible fraction; copying 6/9 will will paste 2/3. Irreducible fractions are fine and do not get modified. Improper fractions are also supported.

I suspect Excel is converting the source data from fraction to decimal then back to fractions because simplified fractions are a logical result of a decimal conversion. If you consider starting with 2/4, 3/6, 12/24 they all convert to 0.5 decimal. We lose all information about there uniqueness once they do that. You can differentiate 2/4 from 3/6 but you can't do that with 0.5 and 0.5 Coming from decimal back to fraction, there are literally an infinite number of fractions equal to 0.5 and we could use any of them. We don't know what we started with because everything was lost moving to 0.5, the odds of guessing accurately when you have an infinite number of choices is rather small, so the best result will be fully reduced and simplified, and there's only one of those... 1/2

Sadly the custom format I recommended last week, "#/#", suffers from the exact same problem as "transition entry". It brings all the fractions over but simplifies every reducible fraction pasted.

  • Thanks. I'll try this when I next have access to a computer some time next week. If you could record a 30s video verifying it works that would be great. Other solutions suggested "match destination formatting" but you can see from my video that no such option exists when I try. Aug 18, 2018 at 1:42
  • Sure. I'll update my answer with screenshots, a write-up, and I'll link a video. I suspect the video will be of limited value but the screenshots will help anyone who doesn't know how to set a custom number format. I am certain I can solve this through VBA, but that requires a lot more work for me so I want to make sure standard Excel doesn't work before I dive off the deep end. Aug 18, 2018 at 2:32
  • 1
    Self answered questions are permitted. Aug 25, 2018 at 18:27
  • 1
    I have to admit I didn't test 5-4 in any scenario because I used the HTML you provided which only included fractions. My macro should work with those but it seems irrelevant now. Aug 25, 2018 at 18:32
  • 1
    If your solution is the best solution for the question then you should post it. Since you asked the question you have a much better sense of the best solution than the rest of us. Also, consider if I edit my post and make a mistake. Or don't satisfactorily explain a critical aspect? If you like my answer then vote it up but post yours Aug 26, 2018 at 22:27

So after lots more research and some very helpful input from @ProfoundlyOblivious, I worked out the best possible method that seems to work. It's far from perfect, but it actually works from all data sources I've tried, doesn't involve convoluted steps and most importantly is time efficient and reliable.

Suggestions such as Get Data From Web or VBA scripts simply are not usable when you have to take data from a variety of unpredictable sources and wrangle it into Excel or CSV format.

Likewise, I verified over and over again with various versions of Excel that the people claiming going via Notepad works or that pre- and/or post-formatting the cells does not work. Sadly, nor do any custom number/text formats. What is clear that data in this format is a clear outlying compared to other data formats when handled by these versions of Excel.

What does work

Avoid Excel!

Copy/pasting into a Google Docs spreadsheet and then simply saving as *.xlsx or *.csv works fine.

It really was as easy as that. Maybe not a true answer to how to fix Excel but by far the best workaround I've found. Quick, easy, reliable and does all I need to do.

  • 2
    You accused teylyn of being disingenuous, but that term really applies to this answer. The question and comments go to great lengths to insist that any solution must use Excel and force it to behave as you wish. You chastised other answerers for not complying with that requirement. So your own answer, which you accepted, is to use a different spreadsheet app instead of Excel? If that was an allowable solution, you should have allowed it in the question, and you would have received the acceptable answer weeks earlier, and without wasting a lot of people's time.
    – fixer1234
    Aug 11, 2019 at 20:32
  • Could you collate the other answers that work without Excel? Aug 12, 2019 at 11:46
  • 1
    There are no other answers that recommend using an app other than Excel because the question and your comments stipulate that any solution must be to fix Excel. In general, when a question explicitly states that it is looking for a solution using a specific app, and someone posts an answer suggesting to use a different app, that is often downvoted and/or deleted as non-responsive. I suspect the upvotes on your answer are a political statement about Microsoft or Excel rather than indications that this is a good solution to what was asked in the question and clarified in comments.
    – fixer1234
    Aug 12, 2019 at 17:46
  1. Copy the data to Notepad. Do not edit in anyway.
  2. Copy the data from Notepad to MS Word.
  3. Find and Replace in Word. Find "^t", Replace with "&".
  4. Open new Excel worksheet, select sheet, format cells as "text".
  5. Copy data from word into the worksheet using Edit Paste Special TEXT.
  6. Use "=LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,99)"* to convert the text in cell A1 to a number. The cell containing the result needs to be formatted from TEXT to GENERAL. (* credit to Myall Blues for the formula as found at https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2016/convert-text-fractions-to-number-decimal/93f0de8a-d96c-4cbf-a677-a3f3937cd46e)
  • Doesn't seem to work. Aug 12, 2019 at 11:47

I don't if it is still actual, but there is very simple solution - copy text and pastes special --> Text. Everything works perfect.

  • Question specifies this is not a solution.
    – Alex M
    Sep 23, 2019 at 20:55

Well, methinks you need to rephrase your question. It's more along the lines of "how can I eat my cake and still have it?"

The way that Excel works, it will try to make sense out of the data and apply default formatting. That applies to text that looks like dates, text that looks like numbers, html tags that imply that something is in a table cell and stuff like that.

If formatting is ignored, for e.g. by pasting text (or paste special Values), then ALL formatting is ignored. The text that looks like dates will not be converted to dates. But the table structures won't survive, either.

If, however, you want to retain the table structure, you'll have to let Excel interpret the pasted content and apply the formats it will recognize. Unfortunately, that means that these texts will get converted into dates.

However frustrated you may be, at the moment there is no out-of-the-box solution for this. What you are asking for can currently not be done. (That is a correct answer to your question).

You may want to post a suggestion in http://excel.uservoice.com where people can vote on it. The more votes a suggestion has, the more likely it is that it will be implemented.

You also may want to consider alternatives to copy and paste. Maybe you could use Power Query to query the data source and shape the data to retain the text as is.

  • 1
    If formatting is ignored, for e.g. by pasting text (or paste special Values), then ALL formatting is ignored. The text that looks like dates will not be converted to dates. But the table structures won't survive, either This is a total straw man. The point is pasting text or values retains the table structure for every other conceivable text string apart from numbers separated by symbols such as "/" and "-". What's more, setting cell data types is not overwritten for any other data type I know of other than dates (especially bemusing as how common it is to have non-date data like this. Aug 16, 2018 at 4:15
  • Let's consider other data formats supported by Excel such as percentage, currency, accounting, date value, scientific. Copy-pasting strings such as 10% $50.63 4.25E+11 results in automatic formatting (again, there should be a simple toggle to turn this off) but converting to text does exatly what it says on the tin. The only the formats I know of where converting to text doesn't convert to the literal string is these sorts of numbers that sometimes are dates. Instead Excel destroys the content of the data by turning 7/8 into 43319 5-4 into 43195 6.4 into 43196 and so on. Aug 16, 2018 at 4:22
  • 1
    Why do you continue complaining in the comments? Those don't clarify your question and don't help to solve it. I understand you're not happy with the answer, however it's not up to us to change Excel. Aug 17, 2018 at 8:47
  • 1
    @Turkeyphant Please stop complaining about what your version of Excel SHOULD do. Excel 2007 is almost 15 years old, considering it took 3 years to plan its features, develop and test them in the old 3-year release cycles. If you complain about that, you might as well complain that your rotary phone cannot receive text messages. As others have mentioned, modern versions of Excel can do what you want to achieve with less hassle than you have to suffer. So, how about upgrading?
    – teylyn
    Aug 17, 2018 at 11:36
  • 1
    It's also disingenuous and utterly mistaken to suggest I'm asking for something unreasonable like having my cake and eating it. In the question I suggest a simple method that would completely avoid this issue and cause no problems for those who like the auto formatting and demonstrate that this is an outlier compared to all other data formats that are treated as expected and intended. Aug 18, 2018 at 1:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .