Check if cell contains partial text string based on string in another cell

[silviosilver]

I want to check whether a cell contains a partial match with a text string in another cell.

Say A1 contains the string ABCDEFG, I want to check if B1 contains any part of ABCDEFG - eg ABC or CDE or DEFG.

But I don't want to have to manually enter "ABC" or "CDE" or "DEFG" in searching for a match, I want this to occur automatically based on the string that is (in this example) in A1.

How can I do this?

 

[RoryA]

What's the minimum length of text that counts as a matching part?

 

[silviosilver]

What's the minimum length of text that counts as a matching part?

Four letters.

 

[RoryA]

So not ABC or CDE then?

Which version of Excel do you have?

 

[silviosilver]

So not ABC or CDE then?

Which version of Excel do you have?

Pardon my lack of specificity. Three letters may well do, but I think a minimum of four would be better. (The actual strings I have are about 20 characters, but quite distinct, so four should do nicely.)

2016 version.

 

[sandy666]

like this?

RawData	IsIn	Contains
ABCDEFG	CDEF	TRUE
ABCDEFG	HJK	FALSE
ABCDEFG	GHI	FALSE
ABCDEFG	B	TRUE
ABCDEFG	DE	TRUE
ABCDEFG	AG	FALSE

 

[RoryA]

It would probably help if you gave us some representative samples of your actual column A and B data. Certainly what I think you want is very different from what Sandy is suggesting, which would be a very simple SEARCH/FIND formula.

 

[silviosilver]

like this?

RawData	IsIn	Contains
ABCDEFG	CDEF	TRUE
ABCDEFG	HJK	FALSE
ABCDEFG	GHI	FALSE
ABCDEFG	B	TRUE
ABCDEFG	DE	TRUE
ABCDEFG	AG	FALSE

Yes, like that.

Although I'd actually prefer if column C returned the portion of string from column B that was found in column A. So C1 in your example would return CDEF rather than TRUE.

 

[silviosilver]

It would probably help if you gave us some representative samples of your actual column A and B data. Certainly what I think you want is very different from what Sandy is suggesting, which would be a very simple SEARCH/FIND formula.

I had in mind the kind of thing Sandy gave as an example. I could do a SEARCH to find it, but didn't know how to do it without manually entering "CDEF". I want Excel to do automatically check if any of column A can be found in column B.

 

[sandy666]

like this?

RawData	IsIn	Contains
ABCDEFG	CDEF	CDEF
ABCDEFG	HJK
ABCDEFG	GHI
ABCDEFG	B	B
ABCDEFG	DE	DE
ABCDEFG	AG

btw. this is case sensitive