Validate Specific Alphanumeric Format

Question

I'm struggling to get this to work. Basically, I'm trying to create an excel formula that will look at a value e.g. RFC12345678 and return whether the format is valid or not. The conditions are:

• Has to begin with RFC
• Followed by 8 numbers

If it meets those conditions, return "Valid", if not, return "Invalid".

Answer 1

Use AND() with three checks:

=IF(AND(LEN(A1)=11,LEFT(A1,3)="RFC",ISNUMBER(--RIGHT(A1,8))),"Valid","Invalid")

• The first makes sure there are no extra characters, so RFC123456789 does not return TRUE;
• The second test for RFC in the beginning;
• The last test if the right 8 are numeric.

If your check needs to be case-sensitive, change to:

=IF(AND(LEN(A1)=11,EXACT(LEFT(A1,3),"RFC"),ISNUMBER(--RIGHT(A1,8))),"Valid","Invalid")