Exclude records that start with *

[Steve 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
tried .. Not Like '~*%' but that does not work. Does anyone have a
suggestion.

Thanks,
Steve

[Michael]

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"


"Steve A" wrote:

> 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

[Sloth]

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


"Steve A" wrote:

> 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

[Steve A]

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.

"Sloth" wrote:

> 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
>
>
> "Steve A" wrote:
>
> > 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