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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Then choose Paste -> Match Destination Formatting.

Answer 6

Try this, it worked out for me:

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

It's so simple.

regards!

Answer 7

options/Transition

transition formula evaluation - enable it

PS

and forget about already pasted and saved data. =(

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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