Search text within cell

[k.roberts]

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?

 

[Ron Rosenfeld]

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

 

[Guest]

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