I have a "single line of text" column in my list that needs to contain zero or more numerical values, separated by semicolons, with no spaces, letters, or other characters.
The contents of that column are later inserted into the query string of a URL pointing outside of my site; the values are 5- or 6-digit numbers that identify documents related to my list's item. The hyperlink thus points to a search results page containing all of the related items on the external site. (I do not have any control of the other site, so I can't for example convert their 5-digit numbers to 6-digit numbers with a leading zero.)
I have added these format requirements to the column's description field, including an example, but apparently nobody reads those, because I still get items with commas instead of semicolons, spaces between numbers, etc. This, of course, causes the resulting hyperlink to provide either a "no results" page, or possibly a partial list of results (with no obvious indication that it's not the full list it should have been).

I had the idea to use the SUBSTITUTE() function to convert all semicolons to zeroes and then check if the result is numeric, but unfortunately SharePoint doesn't support SUBSTITUTE() in validation formulas. Does anyone have any other suggestions on how I can accomplish this?

Examples of valid values:

11111;22333;123456;44444;987654;90210;70207;80808;112358 [...]

Examples of invalid values:

10001, 10002
123456 and 987654

The main difficulties seem to lie in the fact that there can be any number of values, and also that they aren't guaranteed to be a specific length.
If it were only 5-digit numbers, I could write an extremely long validation formula to run two checks: first, that every 6th character is a semicolon, and second, that all other characters are digits. I would just have to make it long enough to account for the maximum of 42 numbers (42 time 5 digits, plus 41 semicolons = 251 characters) that could go into a 255-character text field. But I don't think I could write one that allows for any combination of 5- and 6-digit numbers that way.
Also, I think there might be a limit on the length and/or complexity of the validation formula, similar to the maximum function nesting limit in Excel formulas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.