Simple (I hope) AutoFilter question

[SteveD]

I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

Do any of you have any good ideas on how to get around this little issue?

Thanks,
Steve

[Ricky Morris]

Try adding another column to your data set with the folowing formula:
=if(left(a1,2)="* ","Y","N") assuming your part number is in column A.
Then you can filter on that column for Y


_________________
It's never too late to learn something new.

Ricky

[ This Message was edited by: Ricky Morris on 2002-04-22 12:36 ]

[Aladin Akyurek]

On 2002-04-22 12:15, SteveD wrote:
I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

Do any of you have any good ideas on how to get around this little issue?

Thanks,
Steve

You can use Advanced Filter for this.

Lets say that A4:A7 houses the following sample:

{"model";
"* trax";
"zxa";
"* daron"}

Format the label distinctly, say, as bold and italic.

In A1 enter: model
In A2 enter: "* " [ without quotes ]

Activate A5.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter as List range:

$A$4:$A$7

Enter as Criteria range:

$A$1:$A$2

Enter for Copy to e.g,

$E$1

Click OK.

[SteveD]

Thank you!

Aladin, yours stil showed me selecting everything (I think that Excel thought I was usind the criteria row to say select all).

Rick, That will work nicely. I'll add a macro to add a row with this formula, filter the sheet, then delete that row. Perfect.

Thanks again for the help,
SteveD

[Aladin Akyurek]

On 2002-04-22 12:50, SteveD wrote:
Thank you!

Aladin, yours stil showed me selecting everything (I think that Excel thought I was usind the criteria row to say select all).

Rick, That will work nicely. I'll add a macro to add a row with this formula, filter the sheet, then delete that row. Perfect.

Thanks again for the help,
SteveD

This is what I get in E1 and down from the sample I posted:

{"model";
"* trax";
"* daron"}

Aladin

[Mark W.]

On 2002-04-22 12:15, SteveD wrote:
I'm working with a list where I want to filter the items that begin with "* "(a star then a space) and then have a string of letters and numbers after it (it is a model number).

I turn on the auto filter and I select "custom" for that column. I select "begins with" in the first drop down box and then type * in the second box and type. As I'm sure you know, that is the wild card indicator and I fail to filter out the other data. (I've also typed "*" and "* " to no avail).

Then I figured I try to do a find/replace on the * (this way, I thought, I could then just sort by what ever I replaced the * with) but I run into the same wildcard issue.

Do any of you have any good ideas on how to get around this little issue?

Thanks,
Steve

Simply apply the custom filter...

Begins with [~* ]

Note: The square brackets shown above are used to delimit the proper pattern. Don't include them in your match pattern.

[ This Message was edited by: Mark W. on 2002-04-23 07:25 ]