Google Sheets - Select rows which not contain specific words-CodePudding

I had a Google form where people have selected options from a check list. e.g.

[] London / Manchester
[] Hong Kong
[] Paris
[] Rome
[] Other: [text]

My responses are in Google Sheets. Some people have selected options 1 to 4 as well as 'Other' and typed their message, e.g.

London / Manchester, Paris, I want to go to Tokyo
London / Manchester, Hong Kong
Paris, Singapore please
Amsterdam

How do I query my spreadsheet to select rows where people have typed a message in the Other option. Hence, the only rows that should show are:

London / Manchester, Paris, I want to go to Tokyo
Paris, Singapore please
Amsterdam

Thanks

UPDATE: Alternatively, can I somehow filter out the rows using a list of keywords so it only shows text that was entered for Other?

So if a column had these values below, can I use a keyword list such as 'London / Manchester', 'Paris', 'Hong Kong' and 'Rome' to hide these rows but show if text was added to Other option?

London / Manchester, Paris, I want to go to Tokyo
London / Manchester, Hong Kong
Paris, Singapore please
Amsterdam

Thanks

CodePudding user response：

You could try

=ArrayFormula(filter(A:A,len(regexreplace(A:A," ?(London / Manchester|Hong Kong|Paris|Rome),?",""))))

CodePudding user response：

if column Other is D column you can try:

=QUERY(A:D; "where D is not null"; )