What is the best way to create the equivalent of a “Regular Expression” (i.e., ^([1-9]{2}/[A-z]{6}(N)\d{6})$) for a “VALID_IF” expression to create a data entry mask for column Data Validity?
Would you please open your expression with your own words so it would be easier for people to give an answer, thanks.
Total of 18 characters as follows:
The first two characters must be digits (1-9)
The next character must be a backslash ("/")
The next 6 characters must be letters
The next 3 characters must be “(N)”
The last 6 characters must be digits (0-9)
(in “Regular Expression” Code: ^([1-9]{2}/[A-z]{6}(N)\d{6})$
"next 3 characters must be “(N)”… do you mean they need to be like “NNN”?
No, I mean the literal characters “(N)”, i.e., open para, N, close para
Everything except “next 6 characters must be letter” are easy to do. I need to think how it could be possible. I assume special characters are not allowed… like [,(,@ etc.?
OK, how would you do the rest (excluding the 6 text characters)? In other words, I am interested in how to create this type of data validation mask in general (in addition to this particular format).
As for the text, is there a way to reference ASCII codes 65 to 122?
What if we allow ANY character for those “6 text characters” to begin with? Update later if we/you find a way to limit to ASCII…
It could be something like…
AND(
NUMBER(LEFT(“12/abcdef(N)123456”,2))<100,
NUMBER(LEFT(“12/abcdef(N)123456”,2))>0, (assuming the first two digits are not “00”)
MID(“12/abcdef(N)123456”,3,1)="/",
IN(MID(“12/abcdef(N)123456”,4,1),LIST(a,b,c,d,e,f)),
…
IN(MID(“12/abcdef(N)123456”,9,1),LIST(a,b,c,d,e,f)),
CONTAINS(MID(“12/abcdef(N)123456”,10,3),"(N)"),
NUMBER(MID(“12/abcdef(N)123456”,13,6))<1000000,
NUMBER(MID(“12/abcdef(N)123456”,13,6))>0 (assuming these last 6 digits are not “000000”)
)
Oh, what I wouldn’t give for regular expressions in AppSheet…
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |