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.
Example
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.
- Copy the original Data
- Paste to Notepad (Prefer Notepad++)
- Change the Cell Properties to TEXT
- Copy All from Notepad
- 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!
Best Answer
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
Text
Copy
the HTML dataPaste
usingMatch Destination
Method 2 - Get Data From Web
Text
in the "Transform" group of the Home tab on the ribbonMethod 3 - VBA
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
Method 4 - Transitional entry (situational)
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.