match one cell for another one that contains it

[krayziez]

I'm trying to do a true/false for a cell that contains info from another
cell:

A1 = auto
A2 = automobile


This is the closest as I can get:
=ISNUMBER(MATCH("A1",E2,0))

I don't know how to do a "contains" function where all of A2 contains
part of A1 to set it to True.

Thanks in advance for the help! This forum is great!

 

[Pete_UK]

You could use the SEARCH function (or FIND function if case matters) to
see if the sub-string is contained in another string. Check out Excel
help for further details.

Hope this helps.

Pete

 

[Ron Coderre]

Krayziez

I find this approach useful....

For
A1: Auto
A2: Automobile

Test if A2 contains A1:
A3: =COUNTIF(A2,"*"&A1&"*")=1

Test if A2 starts with A1:
A3: =COUNTIF(A2,A1&"*")=1

Does that give you something to work with?

Regards,
Ron

 

[krayziez]

Yep that works! So this code:
"*"

means that I want to use a wildcard before and after the cell valu
right? I wonder why I can't just use * .. I guess they'll think usin
* is for multiplying

 

[Ron Coderre]

krayziez

That's correct.....To include the asterisk wildcard, you need t
construct a text string. Hence the quotes and the ampersands (&) fo
string arithmetic:
"*"&A1&"*"

Regards,
Ron

(this may be a dbl-post....the ExcelTip forum popped up an error scree
on the first attempt