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
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
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
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks