is it possible to do multiple =IF(ISERROR(SEARCH("example",A1))...

Hello Jceg Try this

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,"super fun","moderate fun"),"example","real deal"),"text","Afraid")

You can add another substitute and define the word to get converted

if you are satisfied with the answer click " * " at the left hand corner

Hi,

Assuming your list of words to be replaced (i.e. "example" and "super fun") is in Sheet2 A1:A2 and the corresponding replacements (i.e. "real deal" and "moderate fun") in Sheet2 B1:B2, and that your list on which you wish to apply these changes is in Sheet1 A1:A5, enter this array (important) formula in Sheet1 B1 and copy down as required:

=SUBSTITUTE(A1,INDEX(Sheet2!$A$1:$A$2,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A$2,Sheet1!A1)),0)),INDEX(Sheet2!$B$1:$B$2,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A$2,Sheet1!A1)),0)))

Edit: note that this will not perform multiple substitutions in the same cell - if an entry contains the strings "example" and "super fun", only one of these (the first) will be replaced. For multiple substitutions, you will need to use a solution such as hemesh's, though this has the disadvantage that, for each desired substitution, an additional SUBSTITUTE function must be added to the original formula.

However, if you are certain that only one of these strings will ever be present in a given cell, then the solution I propose has the advantage that you can have as many items in your search/replace table as you wish, and as long as you amend the ranges in the formula accordingly, no future amendments need be made.

Regards

Ah, in that case, and with the same assumptions as in my previous post, all you need is this (non-array, this time) in Sheet1 B1 and copied down:

=INDEX(Sheet2!$B$1:$B$2,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Sheet2!$A$1:$A$2,Sheet1!A1)),,),0))

Regards

Originally Posted by jceg316

=IF(ISERROR(SEARCH("example",A1)),”no”,”real deal”)

Or replace the "no" part in your formula with a similar IF-statement.

BTW what if a cell says "super fun example"?

this formula checks for both "example" and "super fun":
=IF(ISNUMBER(FIND("example",A1)),"real deal",IF(ISNUMBER(FIND("super fun",A1)),"moderate fun",""))

You're welcome!
Thanks for the rep. Please mark your thread as solved, under Thread Tools above you first post. If this is not possible, post a link to your thread in this topic and a mod will do it for you.

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.