I built a scraper for the Twitter account of a local transportation network to find out how many bus and tram rides are cancelled each day. All my results are listed in a Google Sheets table. I now want to build one table sheet with the information on all trams and another one with the information on all busses. They differ in their numeration: tram lines are numbered 1 - 20 (in German "Linie 1", "Linie 2", and so on), whereas the bus numbers are greater than 100.

How can I write a filter command to combine text and number range? That*s what I've got so far, but I don't know how to insert the 1-20 or >100 range behind the word "Linie"...

Additional note: As there are other numbers in each tweet, the number has to follow directly after the word "Linie".

=FILTER(Tabellenblatt1!C:D; REGEXMATCH(Tabellenblatt1!C:C; "Linie"))

CodePudding user response：

You can use REGEXEXTRACT to get the numbers after Linie like this:

REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d )")

With INDEX you'll be able to check the whole range, and since REGEXEXTRACT returns a string you can multiply by one to get its value and compare to your desired numbers:

INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d )")*1)<=20

Then you can use both conditions summed to get those that are under 20 and more than 100:

=FILTER(Tabellenblatt1!C:D, (INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d )")*1)<=20) (INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d )")*1)>=100))