li_Hua:
> > Here it is.
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
