Trying to delete multiple cells that containa specific word (not equation)

J

JeanM

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've had to export a form from QuickBooks Online to Excel so I could manipulate it. Within a column there are 115+ cells that contain a specific word that I need to delete all those cells. Is there something as easy as find and delete, rather than find and replace? I can't believe that I have to delete each cell individually.
 
B

Bob Greenblatt

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've had to
export a form from QuickBooks Online to Excel so I could manipulate it.
Within a column there are 115+ cells that contain a specific word that I
need to delete all those cells. Is there something as easy as find and
delete, rather than find and replace? I can't believe that I have to
delete each cell individually.
Have you tried to replace with ""?
 
J

JeanM

yes, all that does is replace the word with the symbol, but still leaves the rest of the content in the cell also. I need the entire cell deleted. Thanks though!
 
B

Bob Greenblatt

yes, all that does is replace the word with the symbol, but still leaves
the rest of the content in the cell also. I need the entire cell
deleted. Thanks though!
You can search for the specific word bounded by asterisks. then replace
with nothing, and the entire cel contents will be deleted.
 
J

John McGhie

Unless you write an AppleScript (and quite a complex one...) then "No" there
is no way to delete a cell automatically.

You can delete the contents from the cell, you cannot delete the cell
structure itself.

However, there are some dodgy work-arounds you can try (this is not suitable
if the other columns contain valuable data).

The first is AutoFilter. Review the help topic "Filter a list of data" if
you don't already know what it is.

Turn on Autofilter, choose Custom Filter, then set "Does not equal" <Word
you don't want>. The word will vanish.

Select what you have left and Copy. Open a new sheet and Paste. Only the
cells that were visible will be copied.

The second is to use sort.

Create a column to the left of the data. In it, place the digit 1 and fill
down so you have each row numbered with the order it is now in.

Now SORT by the column that contains the bad word. This will place all
those rows together. Delete them. Then re-sort the data by the number
column you created, to get the data back in order.

Hope this helps

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've had to export a
form from QuickBooks Online to Excel so I could manipulate it. Within a column
there are 115+ cells that contain a specific word that I need to delete all
those cells. Is there something as easy as find and delete, rather than find
and replace? I can't believe that I have to delete each cell individually.

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top