VBA Autofilter Check for "A123" (letter, number number number)?

[svendiamond]

Hey guys, how can I use Range.Autofilter to filter out a general alphanumeric code such as "A123" or "X029" (letter followed by three numbers)? I've been Googling and trying everything and I can't get it. The following will show me anything that begins with a letter... but that's as far as I've got:

Range("A1:J1").AutoFilter Field:=7, Criteria1:=">;"

Basically I have a list of terminal codes and sometimes they are incorrect. They should always be "A123" or "X987" so, if there is anything that isn't formatted like that, ("i.e. 'A12' or 'XYZA'") then I want autofilter to show me all the incorrect ones.

Thanks

 

[Marcelo Branco]

Maybe you can use advanced filter with a formula

Assuming the codes in column A, beginning in row 2, to show only the incorrect values the formula would be something like this
=OR(LEN(A2)<>4,ISNUMBER(0+LEFT(A2)),ISERROR(0+RIGHT(A2,3)))

Hope this helps

M.

 

[svendiamond]

Thanks, Marcelo. Was about to post that I used a workaround formula.

Mine was

=IF(OR(LEN(G2)<>4,ISNUMBER(VALUE(LEFT(G2,1))),NOT(ISNUMBER(VALUE(RIGHT(G2,3))))),"ERROR","OK")

But yours works too, and is a bit cleaner.

Thank you!

 

[Marcelo Branco]

Great you found a solution!

You are welcome. Thanks for the feedback.

M.