(v)lookup part of text in cell

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

Hi.

Can anybody solve this problem in excel?
I want to lookup a part of a word in cells fa searching for john and get a
"responce" like this:

Johndoe | yes
john,doe | yes
doejoHn | yes
samual |
Johnace | yes
pete |

I was thinking it could be possible with using wildcards in the vlookup
function, but I don' t know how. It would be nice if capitals are ignored.

Tia
Ted
 
Hi,

yes, you can use wildcards with vlookup:

=IF(ISERROR(VLOOKUP("*john*",A1,1,0),"","yes")

or you can do this:

=CHOOSE(ISNUMBER(SEARCH("john",A1)+1,"yes","")

or:

=IF(ISNUMBER(SEARCH("john",A1),"yes","")

or:

=--ISNUMBER(SEARCH("john",A1)
and then format cell using the following custom style: [=0]"";"yes"

Regards,
KL
 
Try this:

=IF(ISNA(MATCH("*john*",A1,0)),"","Yes")

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
One more:
=if(countif(a1,"*john*")>0,"Yes","")


Hi.

Can anybody solve this problem in excel?
I want to lookup a part of a word in cells fa searching for john and get a
"responce" like this:

Johndoe | yes
john,doe | yes
doejoHn | yes
samual |
Johnace | yes
pete |

I was thinking it could be possible with using wildcards in the vlookup
function, but I don' t know how. It would be nice if capitals are ignored.

Tia
Ted
Click to expand...
 
Back
Top