Converting Strings in Excel to Dates

Question

We imported a bunch of data into cells. But when you click on the cell, their is an apostrophe before the data, so it thinks its a string. By removing the apostrophe, the data gets recognized as dates. How do I avoid doing this by hand?

Answer 1

Use a column beside the dates column.

Add a formula like this one:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

it will vary depending on your date format, copy this formula for all rows.

Then copy the results, and do a paste special where you paste values.

You now have a column with dates.

Answer 2

Yep, go for Find Replace (Ctrl + H)

find: ' //apostrophe replace with: //leave this blank

click "replace all"

Answer 3

I don't think the find and replce would would and the apostrophe is not part of the text, but a excel "feature". depending on how many you have to change - e.g. number of columns the simplist way I can think of without writing a macro would be.

Assuming your dates are in column A.

In a blank column put the following

=VALUE(A1)

This will store the date as a number. replicate the formulae down the column and then copy the values over the original dates. Apply cell formatting to short/long date if needed.

Hope this helps