I have one column with concatenated words, say international city names. Each cell in this column is different, but city names overlap. And I have one cell with concatenated words, say only German city names.

I want to have the international cities limited to just the German cities specified in the control cell, if any of these appear in the cell of the data column. So, one cell might be empty (no German city), one might just be Frankfurt,Munich, another one Berlin,Hamburg,Munich, provided the control cell includes these cities.

If much easier the control cell could also be another column, i.e not a concatenated string.


Preventing certain words from appearing on a Google Sheets

How to ignore certain strings when creating unique list of words in Google Sheets?


1 Answer 1


I believe this formula can solve your problem:

=trim(join(" ";iferror(index(Hlookup(split(A2;", ");split($B$2;", ");1;0)))))

TEST Test sheet

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.