Copying data but removing ignoring cells which contain specific text

Hi everyone,

I have a range of 20 cells (Q13:Q32) which is populated by the formulas it contains. I want to copy/paste the contents into the next column, ignoring any cells where the formula has generated the text, "DELETE THIS ROW", removing any gaps where cells in column Q are ignored.

The cells to be removed always contain "DELETE THIS ROW" but some will also contain other data - if the cell contains "DELETE THIS ROW" at all, the entire cell should be ignored - regardless of any other cell contents.

The aim is to remove any blank cells to create a contiguous block of data-containing cells which can then be copy/pasted elsewhere.

Ideally, I'd like to do this with a formula not VBA so it can run on an Android tablet, but this is not critical.

Hope this makes sense. Please see the attached example.

Many thanks in advance.

Andy

Thanks, that's a clever approach and one I never would have considered. Thanks very much for your help!