0

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?

  • You need to give us data for A1. It makes no sense to talk without an example. – contributorpw Aug 29 at 13:32
0

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

  • It works! Thank you. I understand the general logic behind the formulae, but I would like to get a better understanding of where "⚛$1" come from. Do you happen to have a guide for building these expressions? I tried to google it and failed. Sorry if I ask simple questions but I'm totally new to this. – Vladimir Seriapov Aug 30 at 8:15
  • did not work for me... i.stack.imgur.com/0uVic.png – user0 Aug 30 at 11:06
  • 1
    @user0 sorry, there was a missed .* – contributorpw Aug 30 at 17:00
0

=ARRAYFORMULA(QUERY(IFERROR(REGEXEXTRACT(TRANSPOSE(SPLIT(
 REGEXREPLACE(A1, "(- [A-Z][a-z]+ 2019)", "♦$1♦"), "♦")), "- ... 2019")), 
 "where Col1 is not null offset "&COUNTA(IFERROR(REGEXEXTRACT(SPLIT(
 REGEXREPLACE(A1, "(- [A-Z][a-z]+ 2019)", "♦$1♦"), "♦"),  "- ... 2019")))-1, 0))

0

  • Alright. Formula works fine and I understand it's logic up to 1 point: "♦$1♦"), "♦" Newbie question: Is there some sort of glossary to make those expressions? I failed to google it. – Vladimir Seriapov Aug 30 at 8:26
  • 1
    it means "surround the first group with unique symbols ♦" where unique symbol can be anything you wish ♀♂♪♫♥♣♠... unfortunately, there is no good regex documentation, hut there are few on github that may be useful like: github.com/google/re2/wiki/Syntax as for testing ground you can try: regex101.com – user0 Aug 30 at 11:00

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.