| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for strings in a column
You're Welcome!
" wrote: Wow, I love this one :) Thanks. On Jan 23, 10:06 am, Teethless mama wrote: Try this: =SUMPRODUCT(--ISNUMBER(SEARCH({"Laptop","VPN","notebook"},A1))) 0 or this: =SUM(COUNTIF(A1,"*"&{"Laptop","VPN","notebook"}&"* "))0 " wrote: Hi, I have the following items in COL_A: COL_A ------- Mike B GX270 Notebook X LunchRoom New Printer John VPN Spare Laptop Susan WS What I'd like to do, is to set COL_B to "TRUE" when COL_A contains: "notebook" or "VPN" or "Laptop", in a non case-sensitive context. The result should look like this: COL_A COL_B ------- ------- Mike B FALSE GX270 FALSE Notebook X TRUE LunchRoom FALSE New Printer FALSE John VPN TRUE Spare Laptop TRUE Susan WS FALSE I've been trying a VLOOKUP() function but it doesn't work as expected. The MATCH() funciton works but makes a very complicated formula and I'll have to add exceptions to my list so, putting "Laptop", "VPN", "notebook" and so on in a seperate worksheet makes sense. Thanks for your suggestions.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a formula for searching for any one of a list of strings? | Excel Worksheet Functions | |||
| vba searching for strings in multiple worksheets | Excel Discussion (Misc queries) | |||
| Searching for codes in text strings | Excel Worksheet Functions | |||
| searching for multiple text strings | Excel Discussion (Misc queries) | |||
| Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
Hybrid Mode
