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?

Improve this question
1
  • You need to give us data for A1. It makes no sense to talk without an example.
    – contributorpw
    Aug 29, 2019 at 13:32

2 Answers 2

Reset to default
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

Improve this answer
3
  • 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, 2019 at 8:15
  • did not work for me... i.stack.imgur.com/0uVic.png
    – user0
    Aug 30, 2019 at 11:06
  • 1
    @user0 sorry, there was a missed .*
    – contributorpw
    Aug 30, 2019 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

Improve this answer
2
  • 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, 2019 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, 2019 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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