How do I copy cells not in a specific range

[Guest]

Hello,

Please help me with the following Excel question:

I am using the find function and find a list of cells containing a certain
word. Then I highlite them and click on copy. But then I get an error:
"That command cannot be used on multiple selections."

How do I find a list of cells containing a specific word throughout the
worksheet and then copying all of them into another worksheet?

Help would be much appeciated.

 

[Gary.Majdanek]

Do all of the found words appear in the same column, or are they
scattered around the sheet?

If they are in the same column, you can use autofilter to hide the
unwanted rows, then paste the remaining visible rows into a new
worksheet.

HTH

Gary Majdanek

 

[Guest]

Hi Gary,

Thanks for the reply.

They are scattered around the sheet. Is there an easy way to copy all cells
throughout the sheet containing a certain word and then pasting them into
another sheet?

Thanks again in advance.

 

[Max]

.. They are scattered around the sheet.
Is there an easy way to copy all cells
throughout the sheet containing a certain word
and then pasting them into another sheet?

Here's an approach using formulas ..

Assume source data is in sheet: X,
within A1:E20 (say),
and the "certain" word is: Text1 (say)
scattered throughout A1:E20

In another sheet: Y (say),

Put in A1:
=IF(X!A9="","",IF(X!A9="Text1",X!A9,""))

Copy A1 across to E1, fill down to E20
(cover the source data extent in X)

This returns "Text1" in exactly the same cells as X

Then kill all the formulas within A1:E20 with an "in-place":
Copy > Paste Special > Check "Values" > OK

The above would quickly give you the results you're after

And if needed, we could also use TRIM
for extra robustness for the formula in A1, viz:
=IF(TRIM(X!A1)="","",IF(TRIM(X!A1)="Text1",TRIM(X!A1),""))

---

 

[Max]

Typo correction, sorry:

Line

Put in A1:
=IF(X!A9="","",IF(X!A9="Text1",X!A9,""))

should read as:

=IF(X!A1="","",IF(X!A1="Text1",X!A1,""))

---