Using "IF" with a "NAME" search?

[Jo]

Hi,

Say I have a "Name" column which could have someone name like "John
Davis" or "Adam John Nelson"

Now, I want to use "IF" statement so that any name has the word "John"
be considered. For example, using the above example I want to get the
answer like this:

John Davis
Adam John Nelson

Using a statment liks this: If(K2="John", "1","0") wouldn't work
right! Help please....

Thanks,
Jo

 

[Guest]

You can use find or search something like this

=IF(ISERROR(FIND("John", K1)),0,1)

Find is case sensitive while Search is not. You probably want case sensitive
to reduce the number of partial matches that you return. You may want to deal
with John and Johnson by adding a blank charater to the end something like
this...

=IF(ISERROR(FIND("John ", K1)),0,1)

It all depends what you want to do...

 

[Zone]

Jo, if you're talking about VBA,
If instr(k2,"John")>0 then
or, to make sure you don't get parts of longer words like Johnson, include a
trailing space
If instr(k,2,"John ")>0 then
Capitalization could matter. Check VBA help for Instr.
HTH, James

 

[Guest]

=Countif(K2,"*John*")

 

[Guest]

I actually used to know a guy named John Johnson and I had a professor named
Ivan Ivanovich. Your function would lead me to believe that these individuals
were twice as good (which I am sure they might agree with)...

 

[Guest]

Ooops... When I first read your function I thought it would return 2 if it
found multiple instances of John in the same cell. Turns out my brain is
still on vacation but my fingers are at the keyboard...