it1000
asked on
How to delete cells in excel if they match keywords?
Excel 2010. I have a column with text. For every row in the column, if it matches certain keywords, I want the info in that cell deleted. For example, if any rows match *spoon* (* is intended as wildcard), replace with a blank space. If any rows match *fork*, replace with a blank space.
How can I do this without doing a find-replace for every single keyword? I have over 30 keywords so doing it manually is tedious.
Thanks.
How can I do this without doing a find-replace for every single keyword? I have over 30 keywords so doing it manually is tedious.
Thanks.
ASKER
I want a completely empty cell when a keyword is found.
If the keyword is fork, and a cell contains "shiny new fork" I want the cell empty or replaced with a blank space.
In your macro, it says
SearchItems = Split("INPUT TEXT HERE, INPUT TEXT HERE", ",")
Why two variables separated by comma? I expected only one. How is the above used?
For example, SearchItems = Split("fork, ________", ",")
What do you enter in ___________
If the keyword is fork, and a cell contains "shiny new fork" I want the cell empty or replaced with a blank space.
In your macro, it says
SearchItems = Split("INPUT TEXT HERE, INPUT TEXT HERE", ",")
Why two variables separated by comma? I expected only one. How is the above used?
For example, SearchItems = Split("fork, ________", ",")
What do you enter in ___________
You would enter the next term, so it would be:
SearchItems = Split("fork, spoon, knife", ",")
The function "Split" will separate the string presented into individual items based on the specified delimiter, in this case the comma. If you separated them with a space the string would look like this:
SearchItems = Split("fork spoon knife", " ")
The output for this would be:
fork
spoon
knife
The code can then search on each term individually and see if the term is found in the cell, then delete it.
SearchItems = Split("fork, spoon, knife", ",")
The function "Split" will separate the string presented into individual items based on the specified delimiter, in this case the comma. If you separated them with a space the string would look like this:
SearchItems = Split("fork spoon knife", " ")
The output for this would be:
fork
spoon
knife
The code can then search on each term individually and see if the term is found in the cell, then delete it.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
It works great. Thank you.
One more question, how can I do the opposite? Meaning, delete cells that DO NOT contain any of the keywords.
Thanks!
One more question, how can I do the opposite? Meaning, delete cells that DO NOT contain any of the keywords.
Thanks!
Thanks, it1000.
The opposite requires a different approach. What about...
- Colour all cells which DO contain any of the keywords.
- Blank all non-coloured cells.
- Remove the colouring.
Regards,
Brian.
The opposite requires a different approach. What about...
- Colour all cells which DO contain any of the keywords.
- Blank all non-coloured cells.
- Remove the colouring.
Regards,
Brian.
Best option would be a macro.
The things we need to know first are:
Will you delete just the cell that contain the keyword; or just the actual keyword in the cell?
Eg "basic fork" is in a cell. Is the result you are after "basic " or a completely empty cell?
This link has the macro that should cover you based on my interpretation of your requirements. If it is incorrect please clarify and I will modify the code.
http://www.teachexcel.com/free-excel-macros/m-24,delete-rows-based-on-criteria.html