Paste the answer (not the formula) into a new column

I had dates in 2 formats in 1 column both 01/10/2016 and 1 October 2016. In order to correct the error and allow sorting of the columns I created another column and formulated =DATEVALUE(A2).

This worked producing a number tat I could format as Date 01/01/2016 but the data remains linked to the previous cell. I want to copy this and paste the answer (not the formula) into a new column. In other words removing the link and just adding the actual data. Can I do that?

Have you tried menu Edit → Paste Special, then again Paste Special … and choose only Date & Time? Whether it works or not might depend on how your data is formatted.


Hello - you could use Data -> Calculate -> Formula to Value to turn all your formulas into values, without the need to Copy and Paste Special. Just select the cells containing the formulas you want to turn to values; all other formulas not selected are kept.

PS: You could have get the result even by using Data -> Text to Columns and using fixed width with selecting the correct column format.

Hope that helps.

Thank you, easier than I thought