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.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
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.