Checking info against cells containing multiple values

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

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

On Sat, 17 Dec 2005 04:44:11 -0000, systematic
<[email protected]> wrote:

>
> 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

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