Checking info against cells containing multiple values

[systematic]

Hi there,

I'm trying to find out if there is a worksheet function that can check
if a value exists in a cell, where that cell may contain multiple
values.

My example, is with resources. I am exporting resources from calendar
to excel - so the 'required attendees' field may have up to 15 or so
unique values (names) in each cell, seperated by a ';'.

I have developed code that will basically split each name into it's own
cell - but this is a messy way to do things.

I'm thinking there must be a way in excel to return a TRUE/FALSE (or
similar) on whether a value exists in a cell containing multiple
values.

eg. A1 may contain -

Smith, John; Stewart, James; Jones, Elizabeth

I'm chasing a function that does -

IF A1 -CONTAINS- 'Stewart, James' THEN A2=TRUE, ELSE A2 = FALSE.

Or something along those lines.

If someone can point me to a function or example I would be grateful!

Cheers

Rob

 

[Darren Hill]

It sounds like you're looking for the Search function

=If(iserror(Search("Stewart, James","A1")),False,True)

Note: Iserror(function) is True if the function it checks returns an error.
Search checks a cell for text; if it finds it, it returns a number (the
position in the cell); if it doesn't find it, it returns an error.


Darren

 

[systematic]

Perfect! Thanks mate - I can work that into my function nicely!