how do i delete using COUNTIF for specific values?

[mike.tsang]

i want to use a COUNTIF formula to delete cells if they contain a
certain string of characters.

for example, i want the formula to delete any cell that contains "_ue"
the top 3 should be deleted.

or instead of deleting, it could also return a value like yes/no so i
could sort and delete.

48_ue13515
54615_ue1785
ao238_ue
gs65fdg55

if possible i'd rather use a formula than a macro.

 

[Pete_UK]

A formula can't delete a cell - it can only mark a cell (or row) for
deletion using a message like "yes" or "no". Assuming your values are
in column A starting with A2, you could use a formula like this in B2:

=IF(AND(ISNUMBER(SEARCH("_ue",A2)),COUNTIF(A$2:A2,"*_ue*")
<=3),"yes","")

then copy this down. It will mark the first 3 cells which contain
"_ue" with a "yes", so you can easily find them by using autofilter.

Hope this helps.

Pete

 

[mike.tsang]

thanks Pete! such a quick response and that worked perfectly.

how could i modify it so that it can search for more than one? i.e. if
cell contains "_ue" or "_sy" or "_hh" (etc.), then it will show "yes"