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