Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel help in removing certain type of data 1

Status
Not open for further replies.

AlStl

MIS
Oct 2, 2006
83
US
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
 
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.
 
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
 
Try this...

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

 
SkipV,

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

I learned something today!!

Al
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top