Google Sheets – Extract Last Match from String with REGEXEXTRACT

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregexextract

I've created a simple text filter:

=IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE)

It should extract the month and year for an event from the cell A1. A1 contains a lot of text.

It works, but there's a problem: this filter grabs the first match while there can be several events described in A1. What is the proper way to request the last REGEXEXTRACT match from a string?

Best Answer

There is my idea. But I think it's not a cute

=REGEXEXTRACT(
  REGEXREPLACE(A1,".*((Jul|Jun) 2019).*", "⚛$1"),
  "[^⚛]+$"
)

for the string 0dfsa sd - Jun 2019 sadfdf sdf - Jul 2019 asdfasdf - Augx

There is another regexp ".*(Jun 2019|Jul 2019).*" It also works fine.

My final fantasy is

=IF(
  ISERROR(
    REGEXEXTRACT(
      A1,
      "- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019")
  ),
  FALSE,
  REGEXEXTRACT(
    REGEXREPLACE(
      A1,
      ".*(- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019).*", "⚛$1"
    ),
    "[^⚛]+$"
  )
)

There is my example

Related Topic