Search code examples
regexgoogle-sheetsgoogle-sheets-formulatextjoin

Filter data from cells that are a specific value and another

When I import data via IMPORTXML it is collected as follows:

Goiania
Rio Arena
Free Ticket
LINEUP
Alok
David Guetta
Steave aoki
Dupress
Re dupre
SUBSTITUTES
Vinne
Rainbow
Jow Gonzales
Ilambach

I would like to know what the formula would be like to be able to capture only the values that are between "LINEUP" and "SUBSTITUTES". Because this import the values between these two words will change constantly, both in number of filled lines and in the names of the artists, so I need something that can fit in with the changes.

I have tried to calculate the number of characters between the two words, but I was unsuccessful at separating this data. I was just mistaken.

Solution

  • replace A1:A14 with your IMPORTXML formula

    =TRANSPOSE(SPLIT(REGEXEXTRACT(TEXTJOIN("♦", 1, A1:A14), "LINEUP(.*)SUBSTITUTE"), "♦"))

    0