I want to separate text from cells:

A1 = Bats, Cats, Mats, Rats E

A2 = Bats, Pattern, Rats W

A3 = Cats, Mats

A4 = Pattern

A5 = Mats

I want to remove Pattern and Mats from the rows and extract rest of the text separated by comma in one row in google sheet.

Like D1 = Bats, Cats, Rats E

D2= Bats, Rats W

D3 = Cats

D4 = ""

D5 = ""

  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Aug 30, 2021 at 22:45

1 Answer 1


Try this in cell D1:

=arrayformula( regexreplace( regexreplace( regexreplace(A1:A5, "(Pattern|Mats)", ""), ", , ", ", " ), "^, |, $", "" ) )

To learn the exact regular expression syntax used by Google Sheets, see RE2.

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.