Search text within cell

  • Thread starter Thread starter k.roberts
  • Start date Start date
K

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?
 
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?
Click to expand...

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