Search code examples
excelvalidationexcel-formulaalphanumeric

Validate Specific Alphanumeric Format

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".

Solution

  • 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")