Regular Expressions?

[Neil_Boisen]

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?

[Aleksi]

Would you please open your expression with your own words so it would be easier for people to give an answer, thanks.

[Neil_Boisen]

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

[Aleksi]

"next 3 characters must be “(N)”… do you mean they need to be like “NNN”?

[Neil_Boisen]

No, I mean the literal characters “(N)”, i.e., open para, N, close para

[Aleksi]

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

[Neil_Boisen]

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?

[Neil_Boisen]

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…

[Aleksi]

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

[Steve]

Oh, what I wouldn’t give for regular expressions in AppSheet…