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?
8
-
sounds like your import wasn't accurate ... how did you import?DarkSquirrel42– DarkSquirrel422011-06-02 00:09:52 +00:00Commented Jun 2, 2011 at 0:09
-
another programmer imported it, and he's not here, so I need a quick way to get rid of the apostrophe's without a macrocdub– cdub2011-06-02 00:11:04 +00:00Commented Jun 2, 2011 at 0:11
-
1without a macro or reimport? no chance ...DarkSquirrel42– DarkSquirrel422011-06-02 00:11:42 +00:00Commented Jun 2, 2011 at 0:11
-
1Have you tried Find and Replace? Replace it with ''.garnertb– garnertb2011-06-02 00:16:39 +00:00Commented Jun 2, 2011 at 0:16
-
1You can't do a find and replace since Excel says it can't find the character (the character only shows up in the formula bar). This code was an export from outlook, which we need changed into db type datetime.cdub– cdub2011-06-02 00:25:41 +00:00Commented Jun 2, 2011 at 0:25
|
Show 3 more comments
3 Answers
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.
Comments
Yep, go for Find Replace (Ctrl + H)
find: ' //apostrophe replace with: //leave this blank
click "replace all"
Comments
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
