Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why am I getting XXX error in text cell with word wrap turned on? | Excel Discussion (Misc queries) | |||
Text in formula bar is not displaying in cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
how to hyperlink text to a cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |