Validate Alphabetic Characters in a Cell

Question

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.

JvdV · Accepted Answer · 2022-08-24T12:02:22.537

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

Thank you. Not something I have used before but works like a charm. — Jason, Aug 24 '22 at 14:03

Tom Sharpe · Answer 2 · 2022-08-24T19:39:22.180

Just an excuse for me to try out the shiny new Advanced Formula Environment

You can create more extensive lambdas like this:

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

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

Validate Alphabetic Characters in a Cell

2 Answers2