I have user-input strings of size codes in one column which follow this format:
[3][4][5][6][7][8][9][10][11][12][13][14][15][16][17]
[3][3-][4][4-][5][5-][6][6-][7][7-][8][9]
[XS][S][M][L][XL]
The input could be any length, and the values may not be in order, but they should always be delimited using square brackets.
I need to validate these user-input strings against another column which contains a string of all possible values for that row, e.g.
[3][3-][4][4-][5][4-][6][6-][7][7-][8][8-][9][9-][10][10-][11][11-][12][12-][13][13-][14][14-][15][16][17]
[XXS][XS][S][M][L][XL][XXL][3XL][4XL]
Again these reference strings could be slightly different for each row, and they might not be the same length, however within the reference the delimiters will always be correct and the sizes will always be in a specific order, even if some are missing.
The validation needs to do two things:
- Check that the delimiters used are correct (no typos)
- Check that there are no values within the user-input string that are not contained in the reference.
If the user input contained a fixed number of values and all possible values within the string had the same character length I could do this relatively easily using an inelegant combination of LEN() and SUBSTITUTE() functions, but I'm struggling to factor in the variable lengths of both value and string: the different value lengths and the fact that some values contain others (e.g. 13 and 3- both contain 3, XXL contains both XL and L) will play havoc with the SUBSTITUTE() function and the variable number of values contained within a string makes it impossible to tell the formula how many substitutions to make, unless I use VBA loops which I really don't want to do.
I know this would be a lot easier if the validation reference was a list rather than a string, but I can't change this as it's fetched from a database. Nor can I add helper columns to the table to split the user input values out because it will mess up a load of database commit procedures.
I've also tried using SEARCH() with wildcards but this is not at all reliable.
Am I being stupid, is there a simple answer here? Or am I going to have to crack open the VBA editor for this one?
Bookmarks