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