+ Reply to Thread
Results 1 to 4 of 4

Validate values within a string based on a reference string

  1. #1
    Registered User
    Join Date
    04-09-2021
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Validate values within a string based on a reference string

    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?
    Attached Files Attached Files
    Last edited by KF91; 04-09-2021 at 10:53 AM. Reason: Adding attachment

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    Re: Validate values within a string based on a reference string

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Validate values within a string based on a reference string

    Please Login or Register  to view this content.
    In D2

    =Validate($B2,$C2)
    Last edited by JohnTopley; 04-09-2021 at 01:14 PM.

  4. #4
    Registered User
    Join Date
    04-09-2021
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Re: Validate values within a string based on a reference string

    Thanks John!

    This works perfectly on my end but sadly I can't use VBA UDFs client-side, and I want to use it as a validation formula in Data Validation which doesn't allow UDFs, so I've had to redesign:
    • Amended the database procedure to add a comma between values in the strings in Column B
    • Added =ISNUMBER(SEARCH(SUBSTITUTE(C2,",","*"),B2))="TRUE" into Data Validation
    • Require users to put a comma in if they are typing a string, and require values to be listed in the correct order.
    This shouldn't actually be an issue as 99.9% of the time they will be filling column C by either copy/pasting from column B and deleting out unwanted values, or using a form with check-boxes that generates the string for them.

    Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reference an object via a string (Convert a string to an objejct reference
    By grazian2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2015, 11:47 AM
  2. Replies: 1
    Last Post: 08-07-2015, 04:01 PM
  3. Script to Search for a specific String then return cell values above that String-4
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2015, 01:28 AM
  4. [SOLVED] Script to Search for a specific String then return cell values above that String-3
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 01:50 PM
  5. [SOLVED] Script to Search for a specific String then return cell values above that String-2
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 01:00 PM
  6. [SOLVED] Script to Search for a specific String then return cell values above that String
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 11:28 AM
  7. Replies: 5
    Last Post: 11-08-2012, 03:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1