In Google Sheets return all cells that contain string

Question

I've seen a lot of similar questions where users want to returns all rows that contain a specific value, but I'd like to only return the cells that contain the value.

Basically, I have one sheet that has a bunch of categories in different cells, on the second sheet I'd like to return every category that contains a "_". I tried =QUERY(cat!A:Z,"select * where B contains '_'"), but that returns the whole row, I just want every cell individually.

Thanks for any suggestions!

Answer 1

Try this one:

=TRANSPOSE(SPLIT( ARRAYFORMULA(CONCATENATE(IF(REGEXMATCH(Sheet1!A:Z,"_"),Sheet1!A:Z&"~",""))) ,"~"))

You may use some rare symbol, like ~ to join and split the result, so it shows in one single line.

Data sample

Result