Search text within cell
 

I want to set up a formula that will search for multiple values within
a cell, and then return a value according to the findings.

For example

3 cells may contain the following text:-

B1: Applicants and accepted applicants data for applicants from the
North East of England 1999-2004 entry.

B2: 2005 NMAS application data at King's College London.

B3: sit reps and possibly apptrack

My formula needs to search all cells, for all those that contain NMAS
and/or GTTR and/or sit rep the cell result needs to display C. If it
doesn't contain any of the above terms, then the cell result is blank.

Can anyone help?

Search text within cell
 

On 21 Jul 2006 02:11:02 -0700, "
wrote:

I want to set up a formula that will search for multiple values within
a cell, and then return a value according to the findings.

For example

3 cells may contain the following text:-

B1: Applicants and accepted applicants data for applicants from the
North East of England 1999-2004 entry.

B2: 2005 NMAS application data at King's College London.

B3: sit reps and possibly apptrack

My formula needs to search all cells, for all those that contain NMAS
and/or GTTR and/or sit rep the cell result needs to display C. If it
doesn't contain any of the above terms, then the cell result is blank.

Can anyone help?

Here's one way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Set up your list of words in a Named Range called WordList.

Use this formula:

=IF(REGEX.COUNT(B1,MCONCAT(WordList,"|"))0,"C","" )


--ron

Search text within cell
 

Perhaps something like this:

Using your text samples in B1:B3

C1: =IF(SUMPRODUCT(COUNTIF(B1,"*"&{"NMAS","GTTR","sit rep"}&"*")),"C","")
Copy that formula down as far as you need

OR...if you have many items to match...

E1: NMAS
E2: GTTR
E3: sit rep

C1: =IF(SUMPRODUCT(COUNTIF(B1,"*"&$E$1:$E$3&"*")),"C", "")
(adjust the list and range references to suit your situation.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I want to set up a formula that will search for multiple values within
a cell, and then return a value according to the findings.

For example

3 cells may contain the following text:-

B1: Applicants and accepted applicants data for applicants from the
North East of England 1999-2004 entry.

B2: 2005 NMAS application data at King's College London.

B3: sit reps and possibly apptrack

My formula needs to search all cells, for all those that contain NMAS
and/or GTTR and/or sit rep the cell result needs to display C. If it
doesn't contain any of the above terms, then the cell result is blank.

Can anyone help?