A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Searching String for phrases/multiple text strings



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2009, 09:14 PM posted to microsoft.public.excel.worksheet.functions
Altair1972m
external usenet poster
 
Posts: 19
Default 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  
Old April 20th, 2009, 11:43 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre[_3_]
external usenet poster
 
Posts: 57
Default 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  
Old April 21st, 2009, 04:56 PM posted to microsoft.public.excel.worksheet.functions
Altair1972m
external usenet poster
 
Posts: 19
Default 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  
Old April 21st, 2009, 09:39 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre[_3_]
external usenet poster
 
Posts: 57
Default 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  
Old May 16th, 2009, 10:44 AM posted to microsoft.public.excel.worksheet.functions
Greg Lovern
external usenet poster
 
Posts: 54
Default 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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.