Photo yesteryear Oliver Thomas Klein at Unsplash.
Validating input inwards SharePoint is zilch new, but I haven’t dug much into this over the years. This fourth dimension only about I conduct maintain a listing where i of the fields takes a illustration number. The illustration disclose should last on the format:
0000/000000 <- 4 digits, a slash, together with thence 6 digits.
Using regular facial expression this would conduct maintain been a breeze - \d{4}\/\d{6} , but that’s non an selection inwards SharePoint. Using the official formula documentation every bit a lookup I ended upward amongst the next formula. I’ve split it over multiple lines together with added comments for readability, thence recall to take away the comments earlier using. Depending on your locale you lot mightiness conduct maintain to purpose comma instead of semi-colon.
Make greenback of the +0 which is workaround to convert text to a number.
AND( LEN([Case Number])=11; # Total length is xi characters MID([Case Number];5;1)="/"; # Fifth grapheme is a slash ISNUMBER(MID([Case Number];1;4)+0); # First 4 characters is a disclose LEN(TRIM(MID([Case Number];1;4)))=4; # First 4 characters has no spaces ISNUMBER(MID([Case Number];6;6)+0); # Last half dozen characters is a disclose LEN(TRIM(MID([Case Number];6;6)))=6 # Last half dozen characters has no spaces )
Validation is entered at the columns settings.
And hither I exclusively conduct maintain v digits at the end, which does non validate.
