Finding specific text in a string

[Guest]

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

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

 

[Guest]

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)

 

[Guest]

Thanks Gary

I appreciate the response - and it worked!!!

Have a good day - H

 

[Guest]

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]

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