Stop excel from converting copy-pasted number/text values to date
Solution 1
Try this, it worked out for me:
Data - Import external data - New web query and follow the wizard
It's so simple.
regards!
Solution 2
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.
- 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.
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.
Solution 3
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.
Solution 4
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.
Solution 5
- Open a Blank Workbook
- Click on Data
- Click From Text
- Locate your CSV/txt file
- Click Next
- Choose your Delimiter
- Click Next
- Use the scroll bar to locate the column you wish to protect and click on it
- Select Text from the Column data format
- Click Finish
- Click OK
The data will now have been imported without the date conversion.
Related videos on Youtube
Tomas
Updated on September 18, 2022Comments
-
Tomas over 1 year
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?
-
Tomas over 11 years1) 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 over 11 yearsthank you, nice tip! But it still converts the fields...
-
Tomas over 11 yearsI 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 over 11 yearsUnfortunatelly, this works only when copy-pasting from a web source. If it is copy pasting from MS Access for example, this is unusable.
-
Admin over 11 yearsThe import external data worked for me too with MS Access, you just look for the .mdb source file. Are you working with office 2007+ ?
-
Tomas over 11 yearsYes, but this is complicated - it is much more comfortable to mark the rows you want in Access... I have office 2003
-
Source Matters over 9 yearsThe 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!
-
Tomas over 9 yearsOK, so for million cells you do it million times?
-
DanO over 9 yearsThis works! you can even just start at step 3! Select the whole sheet, format as text, paste! done.
-
DanO over 9 yearsWhy is this the accepted answer? It doesn't address copy/pasting at all.
-
user535673 almost 8 yearsThis 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.
-
theforestecologist over 6 yearsExcel is stupid. WHY DO THEY DO THIS TO US??? However, your solution is the only one that consistently works.
-
theforestecologist over 6 yearsThis does not work for me.
-
theforestecologist over 6 yearsThis does not work for me.
-
Oleg Melnikov over 6 yearsCaution: 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.
-
HongPong about 6 yearsFor formatting, "Text" works and "General" doesn't work. The detail I missed. Thanks Paul.
-
Alekzander over 5 yearsMS advices the same: support.office.com/en-us/article/…
-
Alan Baljeu about 5 yearsNote: Does not work from Word. Does work from Notepad.
-
alfianrehanusa about 3 yearsthis works for me, thanks