MrExcel.com - Your One Stop for Excel Tips & Solutions

Using Advanced Filter for Text searches

Posted by Michael on January 18, 2002 2:46 PM

I have made an unsuccessful attempt to use the advanced filter to filter for exact words in a text list. This is likely an easy question many of you can answer in your sleep, but I would appreciate any feedback.

Issue:

I have a spreadsheet with various columns of data. One of the columns contains strings of text, usually full sentences. Within this column, I have a number of rows of data with key phrases of text (e.g., "free software", "new software", "free offer", "good deal") that I am interested in seeing. I would like to query for specific phrases and then see the results of this query. For example, if I query for "free" I would like to see data for all of the rows that contain the word free.

I have used the wildcard * before words (e.g, *free) to query, but the results are sporadic - sometimes I see the data but often the desired results to do not appear.

I am not looking for any true/false results - I actually want to see the row of data that contains the text I am using to query. The text values used in the query may change frequently as well, so the query needs to be flexible.

Are there additional wildcards I should use? Formatting issues relating to text? Should I use a different approach?

Thank you.

Posted by Aladin Akyurek on January 18, 2002 3:10 PM

Michael --

The following set up simply works:

{"Field1",0,0;
"*free*",0,0;
0,0,0;
"Field1","Field2","Field3";
"free software",2,"Nose Inc";
"new software",3,"New Inc";
"free offer",4,"Samaritan Co.";
"good deal",5,"Calamity Co.";
"our software is now free for 1 dollar",8,"Galaxy Software"}

which is in A1:C9. Zeroes stand for blank cells.

Note the use of *free* in the criteria area, which is in A1:A2.

Aladin

===========

Posted by Michael on January 18, 2002 4:35 PM

Aladin,

Thanks - this is the process I have been using with success historically. However, certain phrases still do not seem to work. As part of the market research I am doing, I often use longer phrases that the query does not seem to recognize.

For example, here is one (below) from the EarthLink ISP site that does not work in the query. I can query for "EarthLink" successfully but not for "free". See if you can get this phrase to work:

"Get EarthLink 5.0 Lite: Want to try the latest version of EarthLink 5.0 software right away? No problem! Now we offer a Lite version of EarthLink 5.0...small enough to download quickly but big enough for you to check out our award-winning software. Download a free copy now."


Michael --

Posted by Aladin Akyurek on January 18, 2002 5:13 PM

> this is the process I have been using with success historically. However, certain phrases still do not seem to work. As part of the market research I am doing, I often use longer phrases that the query does not seem to recognize.

Michael, you're darn right. The length of the problem string is 274, "free" is at pos 261, both bigger than 255. So there. My hunch is that Adv Filter appears to fail because of that fact.

But, don't despair. We're going to use a computed criterion:

In A1 enter: NewFiled (a label that must be different from the label of the column where we apply filter. I've used in my example "Field1", so "NewField" is a different one).

In A2 enter: =SEARCH("free",Field1)>0

Note that I'm using the label of the column of interest in the formula. This requires checking "Accept labels in the formulas" on the Calculation tab.

The rest you know.

Cheers.

Aladin

======== ,

: "*free*",0,0; : 0,0,0; : "Field1","Field2","Field3"; : "free software",2,"Nose Inc"; : "new software",3,"New Inc"; : "free offer",4,"Samaritan Co."; : "good deal",5,"Calamity Co."; : "our software is now free for 1 dollar",8,"Galaxy Software"}


Posted by Michael on January 18, 2002 5:49 PM

Aladin, Thanks for the follow up - the search function you provided does work to return a "True" or "False" if the word "free" is present.
However, based on your answer below, it seems that I am still unable to solve my original question, which was to show the results of the actual complete text string. (Rather than identifying the presence of a word, I need to see the word in its context).

Any way of getting around the 255 limit you noted?

Or, any other computed solutions?

Thanks.


Posted by Michael on January 18, 2002 6:20 PM

Aladin,

My bad! Everything works. I was using one of the filter criteria incorrectly (and likely not following your instructions to the "T". I figured you had to right, so I tried the solution a couple more times and finally had success. I will follow up with any addtional questions.

Thanks again.

Aladin, Thanks for the follow up - the search function you provided does work to return a "True" or "False" if the word "free" is present. However, based on your answer below, it seems that I am still unable to solve my original question, which was to show the results of the actual complete text string. (Rather than identifying the presence of a word, I need to see the word in its context). Any way of getting around the 255 limit you noted? Or, any other computed solutions? Thanks. : Michael, you're darn right. The length of the problem string is 274, "free" is at pos 261, both bigger than 255. So there. My hunch is that Adv Filter appears to fail because of that fact. : But, don't despair. We're going to use a computed criterion