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" wrote in message
...
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