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?

 

[silviosilver]

like this?

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

Yes.

 

[sandy666]

try

Contains = Table.AddColumn(Source, "Contains", each if Text.Contains([RawData], [IsIn]) then [IsIn]  else null)

as the third column
btw. this is case sensitive so ABC <> abc

 

[sandy666]

with this should be easier

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Contains = Table.AddColumn(Source, "Contains", each if Text.Contains([RawData], [IsIn]) then [IsIn]  else null),
    TSC = Table.SelectColumns(Contains,{"Contains"})
in
    TSC

 

[silviosilver]

Thanks, Sandy.

Sorry to be a complete idiot, but what do I do with that? I really have no idea.

I'm unfortunately away from home now, but just playing around on the Excel I have available at the moment, I seem to have achieved my original goal with the simple SEARCH function. I'm not sure why this wasn't working when I tried it at home. (Being half asleep probably didn't help.) I'll have to check a bit later.

 

[RoryA]

It does seem like just some variant of =SEARCH(B1,A1) would do it - perhaps wrapped in ISNUMBER if you just want a True/False result.

 

[sandy666]

to use Power Query you need Excel with Power Query (I don't know which version you have right now, away from home)
with XL2016 : Data tab - New Query - From Other Sources - Blank Query then open Advanced Editor and replace all there with code copied from the post
remember that your source data should be an Excel Table (Ctrl+T) and the name of this table should be the same as in the code (here: Table1)
also adapt the headings to your version of the table
or
as RoryA mentioned you can use SEARCH() (case insensitive) or FIND() (case sensitive)