Wildcard Query to return certain criteria

I have a db with 3000 records. Each record has a unique number that means
something. I want to return all the records that have 0 as the third digit
eg., **0****. What formula do I need to enter to do this?

Thanks,
G

Maybe you can apply Data|filter|autofilter
and do a custom filter of:
begins with ??0

If they values are really numbers, you could use a helper column to convert
those values to text:

=a2&""
then use that same filtering technique.

Gee wrote:
>
> I have a db with 3000 records. Each record has a unique number that means
> something. I want to return all the records that have 0 as the third digit
> eg., **0****. What formula do I need to enter to do this?
>
> Thanks,
> G

--

Dave Peterson

I need two columns.
If your unique number is in column A with no header row, then
In B1: =IF(MID(A12,3,1)="0",ROW())
In C1: =INDEX(A:A,MATCH(SMALL(B:B,ROW()),B:B,0))
Copy B1:C1 down
If you do not start in row 1, change the ROW() so that it yields a "1" in
the first row.
For example ROW()-4 if the data starts in row 5

Someone else might be able to combine these to columns, or maybe just try
what Dave just recommended.

"Gee" <[email protected]> wrote in message
news:[email protected]...
>I have a db with 3000 records. Each record has a unique number that means
> something. I want to return all the records that have 0 as the third
> digit
> eg., **0****. What formula do I need to enter to do this?
>
> Thanks,
> G

Sorry didn't work - only returned some of the data.

"Dave Peterson" wrote:

> Maybe you can apply Data|filter|autofilter
> and do a custom filter of:
> begins with ??0
>
> If they values are really numbers, you could use a helper column to convert
> those values to text:
>
> =a2&""
> then use that same filtering technique.
>
> Gee wrote:
> >
> > I have a db with 3000 records. Each record has a unique number that means
> > something. I want to return all the records that have 0 as the third digit
> > eg., **0****. What formula do I need to enter to do this?
> >
> > Thanks,
> > G
>
> --
>
> Dave Peterson
>

It worked ok for me if the values were text (not numeric) and I included all the
rows in the autofilter range.

Gee wrote:
>
> Sorry didn't work - only returned some of the data.
>
> "Dave Peterson" wrote:
>
> > Maybe you can apply Data|filter|autofilter
> > and do a custom filter of:
> > begins with ??0
> >
> > If they values are really numbers, you could use a helper column to convert
> > those values to text:
> >
> > =a2&""
> > then use that same filtering technique.
> >
> > Gee wrote:
> > >
> > > I have a db with 3000 records. Each record has a unique number that means
> > > something. I want to return all the records that have 0 as the third digit
> > > eg., **0****. What formula do I need to enter to do this?
> > >
> > > Thanks,
> > > G
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson