Search code examples
regexgoogle-sheetsextractgoogle-sheets-formulaarray-formulas

Google Sheets regex keep part of text in each cell

I have a column with the following text in cells:

Colors/Red;Foods/Apple;Category/Featured;

Foods/Banana;Category/Recent;Colors/Yellow;

Colors/Blue;Foods/Grapes;Category/New;

I would like to keep only the Category/Something; text groups in each cell and remove all the other text.

For example I am trying to get the following:

Category/Featured;   

Category/Recent; 

Category/New;

I tried a regex like /Category.*;$/g (Start from Category end at first ;) but it matched until the end of the text, not the semicolon ;

I also tried /Category.{1};$/g but does not work.

Also I am not sure how to keep the matches and remove all the other text.

Solution

  • try:

    =ARRAYFORMULA(IFNA(REGEXEXTRACT(A1:A, "(Cat.*?\;)")))

    0

    =ARRAYFORMULA(IF(A1:A="",, SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFNA(
 REGEXEXTRACT(SPLIT(A1:A, ";"), "^Category/.*"))),,999^99))), " ", ";")&";"))

    0