Finding specific text in a string

Hi All

I have a hundreds cells with a long text string separated by comma's
detailing the all the applications on PC as follows:

Column A, Cell A2: PC Reference
Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator,
Adobe 5.0, etc

....And the list continues to rows 15000

Is there a way I can search the string in B2 to identify 'Project 2000' only
and then populate a blank Cell (C2) with 'Project 2000'? Something like:

IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with
'Project 2000'.

I'd appreciate any advise on how to create such a worksheet function.

Thanks in advance,
H

On Sat, 8 Apr 2006 03:51:01 -0700, Hardip <Hardip@discussions.microsoft.com>
wrote:

>Hi All
>
>I have a hundreds cells with a long text string separated by comma's
>detailing the all the applications on PC as follows:
>
>Column A, Cell A2: PC Reference
>Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator,
>Adobe 5.0, etc
>
>...And the list continues to rows 15000
>
>Is there a way I can search the string in B2 to identify 'Project 2000' only
>and then populate a blank Cell (C2) with 'Project 2000'? Something like:
>
>IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with
>'Project 2000'.
>
>I'd appreciate any advise on how to create such a worksheet function.
>
>Thanks in advance,
>H


C2: =IF(ISNUMBER(FIND("Project 2000",B2)),"Project 2000","")

You can replace "Project 2000" with a cell reference where the cell contains
the application name.


--ron

Try a combination of FIND() and ISERROR():

=IF(ISERROR(FIND("happy",B1,1)),"","happy")

or if using the text in column A will make you more happy:

=IF(ISERROR(FIND(A1,B1,1)),"",A1)

--
Gary's Student


"Hardip" wrote:

> Hi All
>
> I have a hundreds cells with a long text string separated by comma's
> detailing the all the applications on PC as follows:
>
> Column A, Cell A2: PC Reference
> Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator,
> Adobe 5.0, etc
>
> ...And the list continues to rows 15000
>
> Is there a way I can search the string in B2 to identify 'Project 2000' only
> and then populate a blank Cell (C2) with 'Project 2000'? Something like:
>
> IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with
> 'Project 2000'.
>
> I'd appreciate any advise on how to create such a worksheet function.
>
> Thanks in advance,
> H

Ron

Many thanks for that - it worked.

Can you please explain the ISNUMMBER element of the function? Might as well
truely understand how this works :-)

Many thanks again,
H

"Ron Rosenfeld" wrote:

> On Sat, 8 Apr 2006 03:51:01 -0700, Hardip <Hardip@discussions.microsoft.com>
> wrote:
>
> >Hi All
> >
> >I have a hundreds cells with a long text string separated by comma's
> >detailing the all the applications on PC as follows:
> >
> >Column A, Cell A2: PC Reference
> >Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator,
> >Adobe 5.0, etc
> >
> >...And the list continues to rows 15000
> >
> >Is there a way I can search the string in B2 to identify 'Project 2000' only
> >and then populate a blank Cell (C2) with 'Project 2000'? Something like:
> >
> >IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with
> >'Project 2000'.
> >
> >I'd appreciate any advise on how to create such a worksheet function.
> >
> >Thanks in advance,
> >H
>
>
> C2: =IF(ISNUMBER(FIND("Project 2000",B2)),"Project 2000","")
>
> You can replace "Project 2000" with a cell reference where the cell contains
> the application name.
>
>
> --ron
>

Thanks Gary

I appreciate the response - and it worked!!!

Have a good day - H

"Gary''s Student" wrote:

> Try a combination of FIND() and ISERROR():
>
> =IF(ISERROR(FIND("happy",B1,1)),"","happy")
>
> or if using the text in column A will make you more happy:
>
> =IF(ISERROR(FIND(A1,B1,1)),"",A1)
>
> --
> Gary's Student
>
>
> "Hardip" wrote:
>
> > Hi All
> >
> > I have a hundreds cells with a long text string separated by comma's
> > detailing the all the applications on PC as follows:
> >
> > Column A, Cell A2: PC Reference
> > Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator,
> > Adobe 5.0, etc
> >
> > ...And the list continues to rows 15000
> >
> > Is there a way I can search the string in B2 to identify 'Project 2000' only
> > and then populate a blank Cell (C2) with 'Project 2000'? Something like:
> >
> > IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with
> > 'Project 2000'.
> >
> > I'd appreciate any advise on how to create such a worksheet function.
> >
> > Thanks in advance,
> > H

On Sat, 8 Apr 2006 05:04:02 -0700, Hardip <Hardip@discussions.microsoft.com>
wrote:

>Ron
>
>Many thanks for that - it worked.
>
>Can you please explain the ISNUMMBER element of the function? Might as well
>truely understand how this works :-)
>
>Many thanks again,
>H
>

You're welcome.

If FIND succeeds, it returns a number indicating the position of the found
string. So the ISNUMBER is a test for success of the FIND worksheet function.

If FIND does not succeed, it returns a #VALUE! error value, so you could also
use the ISERR, ISERROR, or ERROR.TYPE functions to test for failure of the
function.

You should also note that the FIND worksheet function is case-sensitive. For a
case-insensitive function, you can use the SEARCH function, which will also
accept wild-cards.
--ron