| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching String for phrases/multiple text strings
I have a column of Vendor Comments on the Outreach effort such as
"Client's case is closed" "Client lacks transportation" "Client is 8 months pregnant" "Client moved" "House is vacant" I want to be able to search within that column for words which will flag other columns such as CLIENT MOVED COLUMN words to search: "move" "abandoned" "apartment" "for rent" "no one knows" "vacant" "burned" CLIENT CASE CLOSED COLUMN "Closed" "Post-TANF" BARRIER REPORTED COLUMN "Pregnant" "Transportation" "DV" "Child Care" so that these are autoflagged and so that they will have a 1 in them. I will then filter for column entries and then copy and paste the names of say people who moved into another spreadsheet and send them to someone. My friend introduced me to this phrase =IF(SEARCH(AT$4,$Y18)0,"yes") to try to see if I could find text within another cell. But that only works for one text entry. What I am trying to do is search multiple phrases withing that column and I can't get it to work. I created the 3 columns for flagging. I put the search criteria in the next 3 columns I tried the following 2 methods that did not work: 1) Where Z is the FLAG FOR MOVED column, IF(SEARCH(AS$4:AS$10,$Y9)0,"yes") curiously this one would return only a match for the first criteria unless the two items were on the same line...let me make that one real: If Y9 and Y10 said "Client does not live here" and AS9 said "does not live here", the return would be 1 in Z10 but #Value! in Z11. Whatsupwithdat? 2) IF(SEARCH(AT$4,$Y4)0,"yes",IF(SEARCH(AT$5,$Y4)0, "yes","no")) This one would not search the nested if then. I have about 12 criteria anyway, so could you shed any light on this? I cannot find anything about multiple criteria in the MS Help in Excel. The State of New Jersey thanks you for any useful input. We use Microsoft 2003 in this office. If this cannot be done, someone should pass this on to Microsoft, because it has a lot of applications for future upgrades. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching String for phrases/multiple text strings
With
A2: (a phrase to be searched....eg "Nobody lives at apartment") and G1:I14 containing these values: SearchWord Status move CLIENT MOVED COLUMN abandoned CLIENT MOVED COLUMN apartment CLIENT MOVED COLUMN for rent CLIENT MOVED COLUMN no one knows CLIENT MOVED COLUMN vacant CLIENT MOVED COLUMN burned CLIENT MOVED COLUMN Closed CLIENT CASE CLOSED COLUMN Post-TANF CLIENT CASE CLOSED COLUMN Pregnant BARRIER REPORTED COLUMN Transportation BARRIER REPORTED COLUMN DV BARRIER REPORTED COLUMN Child Care BARRIER REPORTED COLUMN This formula returns 1 if the A2 phrase contains at least one of the SearchWord values: B2: =--(SUMPRODUCT(COUNTIF(A2,"*"&$G$2:$G$14&"*"))0) Copy that formula down as far as you need. Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Altair1972m" wrote in message ... I have a column of Vendor Comments on the Outreach effort such as "Client's case is closed" "Client lacks transportation" "Client is 8 months pregnant" "Client moved" "House is vacant" I want to be able to search within that column for words which will flag other columns such as CLIENT MOVED COLUMN words to search: "move" "abandoned" "apartment" "for rent" "no one knows" "vacant" "burned" CLIENT CASE CLOSED COLUMN "Closed" "Post-TANF" BARRIER REPORTED COLUMN "Pregnant" "Transportation" "DV" "Child Care" so that these are autoflagged and so that they will have a 1 in them. I will then filter for column entries and then copy and paste the names of say people who moved into another spreadsheet and send them to someone. My friend introduced me to this phrase =IF(SEARCH(AT$4,$Y18)0,"yes") to try to see if I could find text within another cell. But that only works for one text entry. What I am trying to do is search multiple phrases withing that column and I can't get it to work. I created the 3 columns for flagging. I put the search criteria in the next 3 columns I tried the following 2 methods that did not work: 1) Where Z is the FLAG FOR MOVED column, IF(SEARCH(AS$4:AS$10,$Y9)0,"yes") curiously this one would return only a match for the first criteria unless the two items were on the same line...let me make that one real: If Y9 and Y10 said "Client does not live here" and AS9 said "does not live here", the return would be 1 in Z10 but #Value! in Z11. Whatsupwithdat? 2) IF(SEARCH(AT$4,$Y4)0,"yes",IF(SEARCH(AT$5,$Y4)0, "yes","no")) This one would not search the nested if then. I have about 12 criteria anyway, so could you shed any light on this? I cannot find anything about multiple criteria in the MS Help in Excel. The State of New Jersey thanks you for any useful input. We use Microsoft 2003 in this office. If this cannot be done, someone should pass this on to Microsoft, because it has a lot of applications for future upgrades. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching String for phrases/multiple text strings
I'm wondering if I did this right because it's returning a 1 no matter what
is in the columns. A B G H I Post TANF 1 Moved Closed Trans apartment is vacant 1 Abandoned Post-TANF Child apartment is vacant 1 client no longer lives 1 out of county 1 out of county 1 Client moved 1 Client could not be reached via phone; left info in clients mailbox during home visit attempt. 1 Client's home was vacant and client could not be reached via telephone. 1 Client's sister stated that client no longer lives there and had no info on how client could be reached. 1 "Ron Coderre" wrote: With A2: (a phrase to be searched....eg "Nobody lives at apartment") and G1:I14 containing these values: SearchWord Status move CLIENT MOVED COLUMN abandoned CLIENT MOVED COLUMN apartment CLIENT MOVED COLUMN for rent CLIENT MOVED COLUMN no one knows CLIENT MOVED COLUMN vacant CLIENT MOVED COLUMN burned CLIENT MOVED COLUMN Closed CLIENT CASE CLOSED COLUMN Post-TANF CLIENT CASE CLOSED COLUMN Pregnant BARRIER REPORTED COLUMN Transportation BARRIER REPORTED COLUMN DV BARRIER REPORTED COLUMN Child Care BARRIER REPORTED COLUMN This formula returns 1 if the A2 phrase contains at least one of the SearchWord values: B2: =--(SUMPRODUCT(COUNTIF(A2,"*"&$G$2:$G$14&"*"))0) Copy that formula down as far as you need. Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Altair1972m" wrote in message ... I have a column of Vendor Comments on the Outreach effort such as "Client's case is closed" "Client lacks transportation" "Client is 8 months pregnant" "Client moved" "House is vacant" I want to be able to search within that column for words which will flag other columns such as CLIENT MOVED COLUMN words to search: "move" "abandoned" "apartment" "for rent" "no one knows" "vacant" "burned" CLIENT CASE CLOSED COLUMN "Closed" "Post-TANF" BARRIER REPORTED COLUMN "Pregnant" "Transportation" "DV" "Child Care" so that these are autoflagged and so that they will have a 1 in them. I will then filter for column entries and then copy and paste the names of say people who moved into another spreadsheet and send them to someone. My friend introduced me to this phrase =IF(SEARCH(AT$4,$Y18)0,"yes") to try to see if I could find text within another cell. But that only works for one text entry. What I am trying to do is search multiple phrases withing that column and I can't get it to work. I created the 3 columns for flagging. I put the search criteria in the next 3 columns I tried the following 2 methods that did not work: 1) Where Z is the FLAG FOR MOVED column, IF(SEARCH(AS$4:AS$10,$Y9)0,"yes") curiously this one would return only a match for the first criteria unless the two items were on the same line...let me make that one real: If Y9 and Y10 said "Client does not live here" and AS9 said "does not live here", the return would be 1 in Z10 but #Value! in Z11. Whatsupwithdat? 2) IF(SEARCH(AT$4,$Y4)0,"yes",IF(SEARCH(AT$5,$Y4)0, "yes","no")) This one would not search the nested if then. I have about 12 criteria anyway, so could you shed any light on this? I cannot find anything about multiple criteria in the MS Help in Excel. The State of New Jersey thanks you for any useful input. We use Microsoft 2003 in this office. If this cannot be done, someone should pass this on to Microsoft, because it has a lot of applications for future upgrades |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching String for phrases/multiple text strings
Using the category list from previous examples:
In G1:G14 SearchWord move abandoned apartment for rent no one knows vacant burned Closed Post-TANF Pregnant Transportation DV Child Care In H1:H14 Status CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT MOVED COLUMN CLIENT CASE CLOSED COLUMN CLIENT CASE CLOSED COLUMN BARRIER REPORTED COLUMN BARRIER REPORTED COLUMN BARRIER REPORTED COLUMN BARRIER REPORTED COLUMN Can you post some Col_A text and the values you want to see returned by the formulas? Example: apartment is vacant.........return: 1 Regards, Ron Coderre Microsoft MVP (Excel) "Altair1972m" wrote in message ... I'm wondering if I did this right because it's returning a 1 no matter what is in the columns. A B G H I Post TANF 1 Moved Closed Trans apartment is vacant 1 Abandoned Post-TANF Child apartment is vacant 1 client no longer lives 1 out of county 1 out of county 1 Client moved 1 Client could not be reached via phone; left info in clients mailbox during home visit attempt. 1 Client's home was vacant and client could not be reached via telephone. 1 Client's sister stated that client no longer lives there and had no info on how client could be reached. 1 "Ron Coderre" wrote: With A2: (a phrase to be searched....eg "Nobody lives at apartment") and G1:I14 containing these values: SearchWord Status move CLIENT MOVED COLUMN abandoned CLIENT MOVED COLUMN apartment CLIENT MOVED COLUMN for rent CLIENT MOVED COLUMN no one knows CLIENT MOVED COLUMN vacant CLIENT MOVED COLUMN burned CLIENT MOVED COLUMN Closed CLIENT CASE CLOSED COLUMN Post-TANF CLIENT CASE CLOSED COLUMN Pregnant BARRIER REPORTED COLUMN Transportation BARRIER REPORTED COLUMN DV BARRIER REPORTED COLUMN Child Care BARRIER REPORTED COLUMN This formula returns 1 if the A2 phrase contains at least one of the SearchWord values: B2: =--(SUMPRODUCT(COUNTIF(A2,"*"&$G$2:$G$14&"*"))0) Copy that formula down as far as you need. Is that something you can work with? Regards, Ron Coderre Microsoft MVP (Excel) "Altair1972m" wrote in message ... I have a column of Vendor Comments on the Outreach effort such as "Client's case is closed" "Client lacks transportation" "Client is 8 months pregnant" "Client moved" "House is vacant" I want to be able to search within that column for words which will flag other columns such as CLIENT MOVED COLUMN words to search: "move" "abandoned" "apartment" "for rent" "no one knows" "vacant" "burned" CLIENT CASE CLOSED COLUMN "Closed" "Post-TANF" BARRIER REPORTED COLUMN "Pregnant" "Transportation" "DV" "Child Care" so that these are autoflagged and so that they will have a 1 in them. I will then filter for column entries and then copy and paste the names of say people who moved into another spreadsheet and send them to someone. My friend introduced me to this phrase =IF(SEARCH(AT$4,$Y18)0,"yes") to try to see if I could find text within another cell. But that only works for one text entry. What I am trying to do is search multiple phrases withing that column and I can't get it to work. I created the 3 columns for flagging. I put the search criteria in the next 3 columns I tried the following 2 methods that did not work: 1) Where Z is the FLAG FOR MOVED column, IF(SEARCH(AS$4:AS$10,$Y9)0,"yes") curiously this one would return only a match for the first criteria unless the two items were on the same line...let me make that one real: If Y9 and Y10 said "Client does not live here" and AS9 said "does not live here", the return would be 1 in Z10 but #Value! in Z11. Whatsupwithdat? 2) IF(SEARCH(AT$4,$Y4)0,"yes",IF(SEARCH(AT$5,$Y4)0, "yes","no")) This one would not search the nested if then. I have about 12 criteria anyway, so could you shed any light on this? I cannot find anything about multiple criteria in the MS Help in Excel. The State of New Jersey thanks you for any useful input. We use Microsoft 2003 in this office. If this cannot be done, someone should pass this on to Microsoft, because it has a lot of applications for future upgrades |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching String for phrases/multiple text strings
Hi Altair1972m,
In the "CLIENT MOVED COLUMN": =IF(itSEARCH(A1,"move|abandoned|apartment|for rent|no one knows|vacant| burned",,9,,,,,,0)0,1,0) In the "CLIENT CASE CLOSED COLUMN": =IF(itSEARCH(A1,"Closed|Post-TANF",,9,,,,,,0)0,1,0) (etc.) To get the itSEARCH() function, you'll need to download and install the Free Edition of inspector text: (it never expires, and it has all the features of the licensed version) http://precisioncalc.com/it For more information on the itSEARCH function: http://precisioncalc.com/it/itSEARCH.html Good luck with your project! Greg Lovern http://PrecisionCalc.com More Power In Excel On Apr 20, 1:14*pm, Altair1972m wrote: I have a column of Vendor Comments on the Outreach effort such as "Client's case is closed" "Client lacks transportation" "Client is 8 months pregnant" "Client moved" "House is vacant" I want to be able tosearchwithin that column for words which will flag other columns such as CLIENT MOVED COLUMN words tosearch: "move" "abandoned" "apartment" "for rent" "no one knows" "vacant" "burned" CLIENT CASE CLOSED COLUMN "Closed" "Post-TANF" BARRIER REPORTED COLUMN "Pregnant" "Transportation" "DV" "Child Care" so that these are autoflagged and so that they will have a 1 in them. *I will then filter for column entries and then copy and paste the names of say people who moved into another spreadsheet and send them to someone. My friend introduced me to this phrase =IF(SEARCH(AT$4,$Y18)0,"yes") to try to see if I could findtextwithin another cell. *But that only works for onetextentry. *What I am trying to do issearchmultiple phrases withing that column and I can't get it to work. I created the 3 columns for flagging. *I put thesearchcriteria in the next 3 columns I tried the following 2 methods that did not work: 1) Where Z is the FLAG FOR MOVED column, IF(SEARCH(AS$4:AS$10,$Y9)0,"yes") curiously this one would return only a match for the first criteria unless the two items were on the same line...let me make that one real: If Y9 and Y10 said "Client does not live here" and AS9 said "does not live here", the return would be 1 in Z10 but #Value! in Z11. Whatsupwithdat? 2) IF(SEARCH(AT$4,$Y4)0,"yes",IF(SEARCH(AT$5,$Y4)0, "yes","no")) This one would notsearchthe nested if then. I have about 12 criteria anyway, so could you shed any light on this? I cannot find anything about multiple criteria in the MS Help in Excel. The State of New Jersey thanks you for any useful input. *We use Microsoft 2003 in this office. If this cannot be done, someone should pass this on to Microsoft, because it has a lot of applications for future upgrades. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for searching for a text string | Excel Discussion (Misc queries) | |||
| Searching for multiple strings and return multiple solutions | 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) | |||
Linear Mode
