|
Searching for strings in a column
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. |
Searching for strings in a column
Enter these words in the cells stated:
D1: notebook D2: VPN D3: Laptop then put this formula in B1: =IF(OR(ISNUMBER(SEARCH(D$1,A1)),ISNUMBER(SEARCH(D$ 2,A1)),ISNUMBER (SEARCH(D$3,A1))),TRUE,FALSE) and copy down to B8. Hope this helps. Pete On Jan 23, 2:21*pm, 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. |
Searching for strings in a column
Well, it surely works, thanks.
On Jan 23, 9:48*am, Pete_UK wrote: Enter these words in the cells stated: D1: * notebook D2: * VPN D3: * Laptop then put this formula in B1: =IF(OR(ISNUMBER(SEARCH(D$1,A1)),ISNUMBER(SEARCH(D$ 2,A1)),ISNUMBER (SEARCH(D$3,A1))),TRUE,FALSE) and copy down to B8. Hope this helps. Pete On Jan 23, 2:21*pm, 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 - |
Searching for strings in a column
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. |
Searching for strings in a column
You're welcome - thanks for feeding back.
Pete On Jan 23, 3:03*pm, wrote: Well, it surely works, thanks. |
Searching for strings in a column
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 - |
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 - |
| All times are GMT +1. The time now is 02:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com