Solved: Data Validation for Text & Number Together - Page 2

li_Hua · 04-23-2020 01:40 AM

Hi,
I would like to implement a container number in the app sheet. Is it possible to validate based on the first 4 characters are alphabets and the next 7 characters numbers and shows that entry is invalid if it does not meet the requirement?

WillowMobileSys

Unfortunately the expression will need to be much more complicated. So you’ll need to use an expression like the below. I have tested it and it works well.

[_THIS] is a special variable that refers to the value, text in this case, of the current column.
The MID() function picks off each single character by its position in the text.
NUMBER() will return zero if the character is alphabetic.
NUMBER() will also return zero if the value is number 0 so we explicitly do not allow zero in the first 4 positions but DO allow “0000000” for the last 7.

AND(
    LEN([_THIS]) = 11,
    AND(NUMBER(MID([_THIS],1,1))=0, MID([_THIS],1,1) <> "0"),
    AND(NUMBER(MID([_THIS],2,1))=0, MID([_THIS],2,1) <> "0"),
    AND(NUMBER(MID([_THIS],3,1))=0, MID([_THIS],3,1) <> "0"),
    AND(NUMBER(MID([_THIS],4,1))=0, MID([_THIS],4,1) <> "0"),
    OR(NUMBER(RIGHT([_THIS],7)) > 0, RIGHT([_THIS],7) = "0000000")
)

I would also recommend including a custom error message for when the format is invalid. An example might be:

"Correct format is 'xxx0000000' - 11 characters with 4 letters followed by 7 numbers"

Customer Message Goes Here:

Message Shows like this

View solution in original post