Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
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... -- HTH... Jim Thomlinson "Jo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
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 "Jo" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
=Countif(K2,"*John*")
-- Regards, Tom Ogilvy "Jo" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
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)... -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: =Countif(K2,"*John*") -- Regards, Tom Ogilvy "Jo" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "IF" with a "NAME" search?
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... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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)... -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: =Countif(K2,"*John*") -- Regards, Tom Ogilvy "Jo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe") add more like "ELB" "FLG" | Excel Worksheet Functions | |||
=IF(ISERROR(SEARCH("insurance",A125,1)),"","*") | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |