2

In google sheets I would like to be able to extract dates from a text cell. I found a solution in RegEx get last match of a date format from string inside a Google Sheets cell but it doesn't take into account that my dates are not formatted equally. They might have 1 or 2 digits for the day, then 1 or 2 for the month and 2 or 4 for the year.

I have adapted the solution from above to look for multple digits using d{1, }:

"(?:.+)(\d{1,}-\d{1,}-\d{2,})" but if the day has 2 digits the first digit falls off

"1-8-2018 en 1-2-2019" results in -> 1-2-2019
"26-3-19 en 23-4-2019" results in -> 3-4-2019
"1-1-20 of 8-2-20 en dan 12-10-20" -> 2-10-20<>

It seems to work fine for month and year but not for the day, how do I solve this?

Bonus question: Mostly there are 2 dates in the cell sometimes 3 maybe more in future. I would like to be able to select which of the dates is returned. It is now selectingt the last date. Is there a way to count the number of matches from the beginning and then choose one?

Jeroen
  • 43
  • 6

1 Answers1

2

You can use

^(?:.*?(\d{1,2}-\d{1,2}-\d{2}(?:\d{2})?)){2}

See the regex demo. This will select the second date in each string due to the {2} limiting quantifier at the end. Adjust the limit number to the one you need.

Details

  • ^ - start of string
  • (?: - start of a non-capturing group (used to group patterns to be quantified here)
    • .*? - any 0+ chars other than line break chars, as few as possible (*? is non-greedy)
    • (\d{1,2}-\d{1,2}-\d{2}(?:\d{2})?) - Group 1 (the actual output): one or two digits, -, one or two digits, -, two digits and then an optional chunk of two digits
  • ){2} - end of the non-capturing group, match two occurrences
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563