I want to build a formula that locates the cells that CONTAIN a certain text
and either replace the WHOLE cell with something else or return a defined
result in another cell.

The first part is pretty much what autofilter does when a custom filter is
defined.