Exclude records that start with *

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve A - 16 Aug 2006 16:03 GMT

I have a query which I am using on a excel list. Some of the records start
with an asterick and I would like to exclude them from the list. I have
tried .. Not Like '~*%' but that does not work. Does anyone have a
suggestion.

Thanks,
Steve

Reply to this Message

Michael - 16 Aug 2006 16:53 GMT

Try adding a column with the following formula:
IF(LEFT(A1,1)="*",MID(A1,2,250),"")
Place the formula lets say on B1; then you set your filter on column B and
select "blanks" and then you will have only those that dont start with a * on
column "A"

> I have a query which I am using on a excel list. Some of the records start
> with an asterick and I would like to exclude them from the list. I have
[quoted text clipped - 3 lines]
> Thanks,
> Steve

Reply to this Message

Sloth - 16 Aug 2006 17:41 GMT

you can use the filter command to filter out the ones with the * and then use
the SUBTOTAL function instead of SUM, COUNT, etc. SUBTOTAL ignores any
filtered items in a list. Under the help you find that SUBTOTAL can do any
of the following...
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Reply to this Message

Steve A - 16 Aug 2006 17:51 GMT

I have done something similar, but I am trying to accomplish this with a
query to save time each time I have to update the list. Any Ideas on a
criteria expression.
Thanks for your help.

> you can use the filter command to filter out the ones with the * and then use
> the SUBTOTAL function instead of SUM, COUNT, etc. SUBTOTAL ignores any
[quoted text clipped - 19 lines]
> > Thanks,
> > Steve

Reply to this Message