I am trying to validate ID's that follow specific conditions. These are:

• 10 character length
• 1st character = numerical
• 2nd character = alphabetical
• 3rd-8th character = numerical
• 9th character = "/"
• 10th character = alphabetical

e.g. 1A234567/B

I am have no problem with most of the formula, but I am stuck how to validate the alphabetic characters at character 2 & 10.

My current formula (excluding the formulas for the alphabetic characters):

=IF(AND(LEN(F2)=10,ISNUMBER(--MID(F2,3,6)),MID(F2,9,1)="/"),"Valid","Invalid")

I hope someone can help ! Thanks.

CodePudding user response：

One, IMHO fun, way to do this is through FILTERXML(). Yes, it's probably more verbose than nested AND() statements, but you could try:

Formula in B1:

=NOT(ISERROR(FILTERXML("<t><s>"&A1&"</s></t>","//s[string-length()=10][concat(substring(.,1,1), substring(., 3,6))*0=0][translate(concat(substring(., 2, 1), substring(., 10)), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][substring(.,9,1)='/']")))

• [string-length()=10] - Test if total length equals 10;
• [concat(substring(.,1,1), substring(., 3,6))*0=0] - Test if when we concatenate the 1st character with the 3-8th character equals 0 when multiplied by 0. Meaning: we validate that these characters are numeric;
• [translate(concat(substring(., 2, 1), substring(., 10)), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''] - Validate that when we translate all uppercase characters to nothing in the concatenated string from the 2nd and last character equals an empty string;
• [substring(.,9,1)='/'] - Validate that the 9th character equals a forward slash.

Note:

• WAAR is Dutch for TRUE;
• You can use this in cell-validation when you would apply a rule;
• FILTERXML() is case-sensitive;
• More on FILTERXML() and the usage of it

  EDIT

  The formula above would be case insensitive. To make it cases sensitive, you could use CODE, but this would need to be wrapped in IFERROR:

  =LAMBDA(cell, lower, upper,
      IFERROR(
          LET(
              length, LEN(lower),
              seq, SEQUENCE(1, length),
              splitCell, CODE(MID(cell, seq, 1)),
              splitLower, CODE(MID(lower, seq, 1)),
              splitUpper, CODE(MID(upper, seq, 1)),
              AND(
                  SUM(
                      (splitCell >= splitLower) *
                          (splitCell <= splitUpper)
                  ) = length,
                  LEN(cell) = length
              )
          ),
          FALSE
      )
  )