Excel help in removing certain type of data 1

[AlStl]

I have a one long list of alphanumeric data approx. 3000 some rows in EXCEL 2010. Following is an example:

PP5690
AG8908
KJ3459
EVENTHORIZON
LISTnumber
UI3409
IL0965

I want to remove all rows that DO NOT conform to value like PP5690 i.e. First two characters are alpha and next four are numeric. The resulting list should look like:

PP5690
AG8908
KJ3459
UI3409
IL0965

Any help will be highly appreciated.

Al

 

[SkipVought]

Hi,

1 the length must be 6

2 the left two characters must be between AA & ZZ

3 the right four characters must be numeric

Assuming that this data is in column A starting on row 1...

=and(len(a1)=6,left(a1,2)>="AA",left(a1,2)<="ZZ",isnumber(right(a1,4)))

Will return TRUE or FALSE.

 

[AlStl]

SkipV,

Thanks for your reply. I tried the formula, but it kept returning FALSE for everything in the list.

Did I do something wrong?

Al

 

[SkipVought]

Try this...

=and(len(a1)=6,left(a1,2)>="AA",left(a1,2)<="ZZ",isnumber(right(a1,4)*1))

 

[AlStl]

SkipV,

Worked like a charm. Thanks a lot for taking time and answering my question.

I learned something today!!

Al

 

[SkipVought]

I needed ti convert the numeric characters to a number before testing if that part of the string was indeed a number. BIG difference between numeric characters and numbers!