Mask for text

[SantiagoS1]

Hi,
First, sorry for my english.
I can’t see in documentation the way to put a mask in valid_if text field.
I need to record licence plate of many cars. My staff make many mistakes when they record the licence plate from android.
Licence plate in my country are always in format 0000LLL (4 numbers 0-9 and three letters).
how can i validate this format rejecting any format different to this?
Thanks

[WillowMobileSys]

Something I recently learned. Try taking a look at the Text() function and see if it can help.

help.appsheet.com

TEXT()

Format as text

[Steve]

There’s no trivial way to do what you want. You could try:

[Plate] = CONCATENATE(
  RIGHT(("0000" & NUMBER(LEFT([Plate], 4))), 4),
  RIGHT([Plate], 3)
)

Unfortunately, there’s no way to ensure the last three characters are letters.

[SantiagoS1]

Thanks. maybe i can reduce the mistakes with this valid_if.
i made some test with ocrtext from licence plate image but results are very random, many times ocr returns wrong values (add country letter or add license plate holder info)

[Fabian_Weller]

Regular Expressions? Questions

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

Maybe that helps.

You should think about using the OCR model instead of OCRTEXT().

[Aleksi]

You should be able to check if the value is text or not with the FIND expression like FIND(UPPER(“a”),LOWER(“a”))=0. If the value is like #, ?,=, etc. the result is always the same and the result is 1. Same with the numbers. The value is 0 only, if the value is letter. Not very elegant formula, but it should be doable like…

FIND(UPPER(MID([_THIS],5,1)),LOWER(MID([_THIS],5,1)))+
FIND(UPPER(MID([_THIS],6,1)),LOWER(MID([_THIS],6,1)))+
FIND(UPPER(MID([_THIS],7,1)),LOWER(MID([_THIS],7,1)))=0

[Steve · Accepted solution]

Or:

AND(
  (LEN([Plate]) = 7),
  CONTAINS("0123456789", MID([Plate], 1, 1)),
  CONTAINS("0123456789", MID([Plate], 2, 1)),
  CONTAINS("0123456789", MID([Plate], 3, 1)),
  CONTAINS("0123456789", MID([Plate], 4, 1)),
  CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MID([Plate], 5, 1)),
  CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MID([Plate], 6, 1)),
  CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ", MID([Plate], 7, 1))
)

[SantiagoS1]

Thanks, it works