Consider the following text in a single column in a spreadsheet:

AAPL 2015 Dec 18 C @ 185.000
AAPL 2016 Mar 18 C @ 160.000

How to sort by date?

I tried extracting the date using regex, but it is a lot of work to convert that to a date object.


Extracting the date with regex, and converting the result with datevalue isn't that much work:

=datevalue(regexreplace(A2, "^\w* (\d+) (\w+) (\d+).*", "$1-$2-$3"))

Then sort by this new column.

Explanation: the three captured groups in the regex are the year, month, and day. The whole string gets replaced by year-month-day, which isn't quite ISO standard (month is in text) but is clear enough for datevalue to convert correctly.

  • It's easy when you know how to do it! Actually, I thought it wasn't parsing because the date is displayed as the epoch. – B Seven Sep 10 '15 at 15:05
  • 1
    Ah yes, if the column with the formula is not formatted as Date, it will show up as a day count. – user79865 Sep 10 '15 at 16:09
  • Oh, so that's the trick. Thanks. – B Seven Sep 10 '15 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.