Replace/Remove exact words

[ryanb4614]

Hello!
In my spread sheet i have Column D (doesn't have to be column specific) I need to remove all the cells containing these words:
MAIL ORDER ITEMS (PACKAGE)
STORE USE SUPPLIES
CONTAINMENT - HOUSES
How can this be done?
------
ALSO I need to: I column A Can all these be done together instead of going to find/replace and one at a time? even if i can do Replace1 at once would cut my time down.
Replace1: 9521,6580,6660,8570 with: 4546
Replace2: 6560,6640 with: 8550
Replace3: 6550, 6630 with: 8540

[Mothman]

Would this be something you would have to always replace? Like a database?

[ryanb4614]

its a spreadsheet i make to upload to my website to update my products.

[martindwilson]

if they are the same structure you can use advanced filter to get all rows in the column that have
MAIL ORDER ITEMS (PACKAGE)
STORE USE SUPPLIES
CONTAINMENT - HOUSES

[ryanb4614]

thank you for the reply. The items I need to remove are in column D only, I am not sure how the advanced feature would help me remove all those items at once?

I have this code for macro but how can i put more words into it to remove?

Please Login or Register  to view this content.

----
Also:
I need to: I column A Can all these be done together instead of going to find/replace and one at a time?
Replace: 9521,6580,6660,8570 with: 4546
Replace: 6560,6640 with: 8550
Replace: 6550, 6630 with: 8540

[martindwilson]

1 Q at a time please!
do you want to remove the whole row or just those cells in col D
you can do the same with the numbers just make your criteria list the same as

9521
8570
6580
6660
in a spare cell at top so it doesnt get filtered out put 4546
apply advanced filter then copy 4546
selected the filtered cells and paste

[ryanb4614]

Well in column D I want to clear those cells and then delete the row those cells are in. Someone PM me with this code, but it freezes excell when i run it...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Mothman]

Well in column D I want to clear those cells and then delete the row those cells are in. Someone PM me with this code, but it freezes excell when i run it...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I changed the range to "selection," just select the cells you want to replace and runt he macro

[ryanb4614]

Thank you I can see that it worked but its frozen and its been 5 minutes since I ran it. There around about 10,000 row. Is this normal?

[martindwilson]

if youd used the filter youd have been done by now lol

[ryanb4614]

if youd used the filter youd have been done by now lol

I just noticied there was an attachment. Okay I couldn't find out how to show everything then when they were removed. But I did data-> show all.. Thank you!

Now how do I replace those numbers that I need replaced?

[martindwilson]

i said before do the same but on column with your numbers in this time.
see my second last post
essentially once filtered you can paste over the filtered list in one go. then repeat for the next list

Replace: 6560,6640 with: 8550
then again for third list
Replace: 6550, 6630 with: 8540

[ryanb4614]

Is there a fast way then to filter them out and paste over those numbers because theres about 1000+ of those numbers. I appreciate your help.

[martindwilson]

depends if you only have those 3 groups it takes seconds

[ryanb4614]

I cannot get it to work for the numbers. It words for the words, but when i run the numbers and click okay nothing is being shown....

[ryanb4614]

scratch that i was putting a * after the number... THANK YOU!!

[ryanb4614]

depends if you only have those 3 groups it takes seconds

one problem i am running into, if I am trying to filter the word col i get collars, color, how do I make it specific?

[martindwilson]

that gets a bit more complicated you have to use formulas to determine what to allow through the filter
we can assume the text will be either of
text space
or space text space
or space text
then you make a function to cater for each in separate rows in adjacent columns
but each one always refers to the first cell in the range you want filtered see attached
edit i think i over complicated the formulas! replace them with
=LEFT(A2,4)="col "
and
=ISNUMBER(SEARCH(" col ",A2))
and
=RIGHT(A2,4)=" col"