Excel - Find any cell with a specific format

Asked By Anthony

20-Nov-09 07:16 PM

I am looking for a formula I can use to determine whether a cell contains
variable data, but of a certain criteria.  For example, I am looking to
determine if column A has an Arizona license plate or any other plate.
Arizona's plates are ABC1234 format.  I tried to use an if statement
=IF(A2<>???####,"Out of State","Arizona Plate") but it returns as an error.
I put quotes around the ???#### and it looks for that specific format.  I
also tried FIND and SEARCH to no avail.

Further clarification: I want to return all these plates as "Arizona Plates"
- RBN5678, IRP0594, NEB7736, and I want to return all these plates as "Out of
State" - 324 ANE, HPE 382, A1 LNK23.  Thanks, as always!!

20-Nov-09 08:44 PM

You test data shows that out of state plates have a space. If this is always
so you could use:

=IF(ISNUMBER(FIND(" ",A3)),"Out of State","Arizona Plate")

HTH
Peter

21-Nov-09 12:10 AM

=IF(
(LEN(SUBSTITUTE(A2," ",""))=7)
*(SUMPRODUCT(--(CHAR(ROW(INDIRECT("a65:a90")))=MID(A2,{1,2,3},1)))=3)
*(SUMPRODUCT(--(CHAR(ROW(INDIRECT("a48:a57")))=MID(A2,{4,5,6,7},1)))=4),

but only according to my interpretation, 3 [A to Z] + 4 [0-9].

HTH
--
MRT