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

krayziez wrote:
> 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!
>
>
> --
> krayziez
> ------------------------------------------------------------------------
> krayziez's Profile: http://www.excelforum.com/member.php...o&userid=34891
> View this thread: http://www.excelforum.com/showthread...hreadid=548034

[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 value right? I wonder why I can't just use * .. I guess they'll think using * is for multiplying.

[Ron Coderre]

krayziez

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

Regards,
Ron

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