Simple (I hope) AutoFilter question

SteveD

Board Regular
Joined
Feb 20, 2002
Messages
104
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top