Home > front end >  Validate Specific Alphanumeric Format
Validate Specific Alphanumeric Format

Time:08-04

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

CodePudding user response:

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