A function to validate Standard / Universal Naming Conventions

zealvert

Hi

It would be really helpful if we introduced a function capable of validating complex patterns of characters, numbers, and symbols in the given cell --in a predefined manner!

Let's assume, the supplier-IDs in a business follow a universal/standard naming convention such as "SUPP001DEL2324 H", "SUPP002DEL2324-M", "SUPP003DEL2324?L", and so on.

SUPP = Denotes a supplier

001 = denotes the supplier's serial number

DEL = denotes region (e.g. Delhi)

2324 = denotes the financial year 2023-24

A blank space or symbol denotes any non-alpha-numeric letter

H = denotes "high priority supplier"

In some large/complex projects or applications, validating such data patterns becomes essential. At present, we have to write and combine extremely complex functions, which is very difficult even for some experts.

So to overcome the above issue, I am proposing below a new function that could validate any such data patterns -- using a very easy syntax. An example follows:

PROPOSED FUNCTION FORMAT:

=Pattern(text, validation code1 [validation code2], ...)

FUNCTION EXAMPLE =Pattern ("SUPP001DEL2324?H",3A,3N,3A,4?,1*,1A)

In the above example:

1. the first argument of the function contains text (SUPP001DEL2324?H) or a cell-reference
2. the next argument (3A) checks if the first 3 letters are alpha (A) or not
3. the next argument (3N) checks if the next 3 letters are numeric (N) or not
4. the next argument is the same thing as in steps 2 and 3 above
5. the next argument (4?) simply ignores the next 4 letters
6. the second last argument (1*) checks if the next 1 letter is non-alpha-numeric
7. the last argument (1A) is the same as in step 2 above.

Please note:

1. The results of the above function could be either true (pattern matched) or false (mismatched);
2. the user should be able to use the pattern matching arguments in any order. And of course, there can be a limit to have a maximum of say, 16 or 32 arguments (depending on Smartsheet's Engineering team's recommendation!).

Once implemented, it would be extremely helpful in numerous situations, especially in the processes/departments wherein a strict, universal/standard naming convention is followed (e.g., employee codes; inventory SKUs; product codes; contract serials; and so on).

Thanks

@Genevieve P. @Paul Newcome @Andrée Starå