Reputation: 429
I have column in excel which has values like "12:00 AM CDT on June 01, 2017" and I need to convert it into format 2017/06/01 00:00
Any lead will be appreciated
Upvotes: 1
Views: 139
Reputation: 2441
Easiest way to do this, if CDT on
part of text is constant, is to use this formula in cell B1
(in case your dates start in cell A1
) and drag it to the bottom of dates. In B1
enter =SUBSTITUTE(A1,"CDT on ","")
, After you substituted all text via formula, then you copy/PasteSpecial value
formulas in same range. Then select whole column with clean dates, go to Data tab
==>Text to Columns
==>choose delimited
type==>press next
until last question==>there select date
type and click finish.
Upvotes: 1