google-sheetsgoogle-sheets-formula

Remove lines from text in a cell that match a set of words


Original text in a cell (e.g., A1):

apple

Apricot

Avocado


Apples

Banana
Blackberry

Cherry

Blueberry

Coconut


Cranberry



Date

Fig

A set of words in a cell (e.g., A2) that must be individually (delimiter ;) checked for matches on each line of text:

Apple;Banana;Cherry;Cranberry;Fig

Each individual word must exactly match (not case sensitive) the value of the line of text.

Expected text in a cell (e.g., A3):

The original text in a cell A1 consists of 26 lines, including blank ones. The result should match the following:

  • Lines 1, 10, 13, 20 and 26 should be removed after match checking.
  • Blank consecutive lines greater than 1 should be replaced with 1 blank line.
  • Blank lines before and after a non-blank value should be removed.
Apricot

Avocado

Apples

Blackberry

Blueberry

Coconut

Date

Solution

  • Try:

    =LET(
      n,CHAR(10),
      s,SPLIT(A1,n),
      JOIN(n&n,FILTER(s,0=COUNTIF(SPLIT(A2,";"),s))))
    

    enter image description here