Conditional Find and Replace

[dhancy]

Hello,

Is there a way to find and replace based on the value of another cell in that row?

For example, in the image below, I want to replace "a" with "xxx" in column B, but only when column A has the value of "yes"

Is there a way to do this, either in a standard formula or in VBA?

Thanks!
Dennis

 

[6StringJazzer]

You can add another column with this formula

=IF(A1="yes",SUBSTITUTE(B1,"a","xxx"),B1)

This assumes you want a partial match. That is, if B has "cat" it will change it to "cxxxt".

If you need to use this result in column B instead of a new column, you can Copy the new column and Paste Values to column B.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

A reasonably simple manual way would be to
- insert a header row
- AutoFilter column A for 'yes' and column B for 'a'
- Select column B data and use F5-> Special -> Visible cells only
- Type xxx and press Ctrl+Enter
- Unfilter