Excel - IFERROR Function

Question

I'm having some problems with my formula and am hoping you can help.

This is my formula:

=IFERROR(REPLACE(F7, FIND("BOB",A2), 13, "Other"),A2)

I have 3 names in my data (Column B):

• Bob
• Sarah
• Lee

The name "Bob" should be located and returned in column C as "Other," and I also want Sarah and Lee to be returned as Sarah and Lee, respectively. I'm not sure why, but my formula now returns everyone's name as "Open."

Have any suggestions?

Answer 1

Use:

=IF(ISNUMBER(FIND("Bob",A2)),"Other",A2)

Remember FIND is an exact match while SEARCH does not care about the case.

So if Bob can be BOB and Bob then you may want to use SEARCH.

=IF(ISNUMBER(SEARCH("Bob",A2)),"Other",A2)