Searching for Substrings Within Strings

[Tiziano]

I would like to be able to search and flag a list of
string data based on another list of substring data.

If I have a list of strings in Column A and input the
list of substrings in Column C, I would like an "X"
to appear in Column B every time that a substring
mentioned in Column C is included in any of the
strings indicated in Column A. (There cannot be
multiple instances of the same substring, or a
combination of different substrings, in each string
of data in Column A.)

For instance, given the following information
indicated in Columns A and C, the "X" should appear
as indicated in Column B.

Thanks in advance for your help.

Column A Column B Column C
----------------- -------- ----------
A6520 04-02 X 6520 04-02
1511 03-01-VT X 1511 03-01
S6520 00
mtr-AAA-TR565
BC6520 04-02-01 X
D6520 00-ABC
8951511 03-01-VTM X

 

[Guest]

I would like to be able to search and flag a list of
string data based on another list of substring data.

Look at the FIND() or SEARCH() functions. The Help files will tell you
all about them.

Bill

 

[Guest]

Hi Tiziano

Try this formulae in B column and i think your problem can be solved.

=IF(ISERROR(FIND(C1,A1)),"","X")

And then you can copy this formulae to any place in the B column where u
want this operation to take place.

Regards

 

[Aladin Akyurek]

B2:

=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X"})

which you need to confirm with control+shift+enter (instead of just with
enter) and then copy down.

C2:C3 houses the search substrings of interest and A2 a target string.

Note. Replace SEARCH with FIND if the evaluation must be case-sensitive.

 

[Tiziano]

Your solution works great, Aladin, except for a tiny
detail...

Say I am going to have a variable number of substrings
in Column C and thus I do not want to include a range
in terms of rows in the function, why does the following
not work?
=LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"})

Am I stuck with something like this?
=LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"","X"})

 

[Aladin Akyurek]

Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following:

E2:

=MATCH(REPT("z",255),C:C)

which calculates the position of the last in use in column C.

The formula needs to be modified:

=LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{0,1},{"","X"})

Still to be confirmed with control+shift+enter.

 

[Tiziano]

Thank you again, Aladin!

While testing your formula, I came across a problem
if one or more of the cells in column C are left
blank while others are populated. (Say, for instance,
I have data in cells C1, C2, C4, but not in C3.
If this happens, then the formula puts an "X" in
column B for every string mentioned in column A.)

I've been thinking about a solution for this, but I
just am not experienced enough to come up with
something. Would you have a suggestion to this
problem?

Thanks.

 

[Aladin Akyurek]

The right thing to do is not to have any empty cells in the range that
must house the substrings to search for.

 

[Tiziano]

I was thinking about linking the spreadsheet
that I am building with another existing one.
The substrings in column C would come from
this other spreadsheet. The only way that I
know how to import data is by using a formula
like this:
=[other_spreadsheet.xls]Sheet1!$A$1
=[other_spreadsheet.xls]Sheet1!$A$2
=[other_spreadsheet.xls]Sheet1!$A$3
etc.

The downside of this is that, if there are blank
cells in col. A of other_spreadsheet.xls, they
will also be imported...

I will start another thread with more information
about my wanting to import data from another
spreadsheet as there are a few more details to
mention and this is deviating from my original
question.

Thanks for your help!!