Thank you for information, here is how I did it.

(1) Select all columns that contain data
(2) Edit, Clear, Format
(3) Delete empty columns
(4) Select all columns that contain data and choose Data, Filter, AutoFilter
(5) Select "Custom" in the dropdown list for the ID field
(6) Set the criterial to "Contains" "W"

This seems to work okay, but I was wondering how else the Contains
option on the "Custom" filter might be used. Would it recognize W########
as a W followed by 8 numbers? And what if you have a column (Field01) that
contains items like A, B, C, D would the Contains option recognize
A,C or how would you enter the criteria to get all the records that
have A or C in Field01.

Les

"Dave Peterson" wrote in message
...
oops. I dropped a ).

=if(and(len(a1)=9,left(a1,1)="w",isnumber(-right(a1,8))),"keepit","deleteit")

Sorry.

Dave Peterson wrote:

I'd add a helper column of cells with formulas like:

=if(and(len(a1)=9,left(a1,1)="w",isnumber(-right(a1,8)),"keepit","deleteit")

Then drag it down the column.

Apply data|filter|autofilter
show the deleteit lines and delete those visible rows.

(maybe start on Row 3, delete row 1 manually and fix row 2 to be the
nicer
headers)

Leslie Coover wrote:

The agency where I work creates various reports (specific records) from
a
large database. When I export it to Excel there is a lot of "garbage"
that
I don't need. (e.g. headings and blank rows that print repeatedly
through
the worksheet.

Consider the following as an example of how the data appears in Excel
once
it is imported from crystal

Work Dept
ID Field01 Field02 Field03 Field04
Work Dept
W12345602 a b c d
W23456725 d c b a
123

W12340678 a b c d
W23356787 d c b a

I want only the records (ID, Field01 through Field04) that contain an
ID number (ID numbers always start with a W and have 8 numeric digits
after the W.

How can I extract the records that contain ID numbers to another sheet
and
leave the garbage behind? Without having to go through the entire
sheet and
hand delete each row that does not contain an ID number.

Thanks,

Les

--

Dave Peterson

--

Dave Peterson