+ Reply to Thread
Results 1 to 12 of 12

Check cell if following specific pattern

  1. #1
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Check cell if following specific pattern

    Hi friends!

    I'm looking to create a formula to validate whether a string follows a set pattern.

    Each book I'm referencing has a set number. This is the specific pattern: "[BOOKS]" + "b" + 3 digit number e.g. [BOOKS]b123
    - Values can be comma delimited
    - No limit to number of books that can be listed

    Here are some examples of cell values and expected outcome of PASS/FAIL.
    1. [BOOKS]b123 > PASS
    2. [BOOKS]b12 3 > FAIL
    3. [BOOK]b123 > FAIL
    4. [BOOKS]b123,[BOOKS]b567,[BOOKS]b876 > PASS
    5. Bonus if I can get it to FAIL if there is a duplicate

    I know it is possible to do this via RegEx, but is there a way to create a formula that does the same validation?

    Thank you!
    Last edited by AliGW; 04-30-2021 at 12:48 AM. Reason: SOLVED tag added - no need to edit the thread title!!!

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Check cell if following specific pattern

    So it seems like you are literally looking for [BOOKS] and this not just a reference to a book title, so having [This is a title] instead of [Books] should generate an error, correct?
    And the comma delimited, is that between the set of values, or can one set of values have a comma, like [BOOKS],123 - is this a fail because there's a comma between [BOOKS] and 123?

  3. #3
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Re: Check cell if following specific pattern

    That's right, literally looking for "[BOOKS]" spelled out, followed by lowercase "b" then 3 random digits.
    [BOOKS],[BOOKS]b123 --> Should fail because it's missing the bxxx following.

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Check cell if following specific pattern

    What about the question about the comma? Is this pass or fail?

    [BOOKS],b123

    Also, I haven't heard of version 2020. Is that the same as MS365? I thought the latest besides 365 was 2019.

    Lastly, is there a limit to the # of times [BOOKS]b123,[BOOKS]b321,... can be repeated?

  5. #5
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Re: Check cell if following specific pattern

    Apologies, you are correct this is Excel for Mac 365 version 16.41

    Comma after [BOOKS] would be a fail.
    Each "value" should follow the "[BOOKS]" + "b" + 3 digit number e.g. [BOOKS]b222
    and it should be comma delimited. If there are any values that don't follow the pattern above e.g. Uppercase "B", More than 3 digits, Missing "["... --> it would be an automatic fail.

    We didn't apply a limit to the number of books that can be listed in a cell, but max number of books we have are 200 if that helps.

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Check cell if following specific pattern

    Well, this might not be the most elegant solution, but at least for what you have stated it seems to work:

    =IF(AND(12*(LEN(A1)-LEN(SUBSTITUTE(A1,"[BOOKS]b","")))/LEN("[BOOKS]b")-1=LEN(A1),ISNUMBER(SUBSTITUTE(A1,"[BOOKS]b",1)*1)),"Pass","Fail")

  7. #7
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Re: Check cell if following specific pattern

    Greg, Thank you so so much! This "not the most elegant solution" was WAY more elegant than my multiple SEARCH calls that I had strung together.
    Appreciate it and this seems to work well for all my test cases!

  8. #8
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: [SOLVED] Check cell if following specific pattern

    Glad it helped. Thanks for the rep!

  9. #9
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Re: [SOLVED] Check cell if following specific pattern

    One last question... If you don't mind can you walk me through the process of coming up with this calculation? Would love to learn how this was put together.
    Specifically this part
    "12 * (LEN(A2) - LEN(SUBSTITUTE(A2, "[BOOK]b", ""))) / LEN("[BOOK]b") - 1 = LEN(A2)"

    Thank you!
    Last edited by naomieatsgreens; 05-01-2021 at 12:18 AM.

  10. #10
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: Check cell if following specific pattern

    This part:
    (LEN(A2) - LEN(SUBSTITUTE(A2, "[STORE]r", ""))) / LEN("[STORE]r")

    tells you how many times the text string of "[STORE]r" is in the cell. Since the total string also includes 3 digits and a comma (12 characters in all), the total amount of characters in the cell would be:
    12*the number of text strings minus 1 because the last string will not include a comma. So if this doesn't match the total length of the characters in the cell, it means that there are either too many or not enough numbers after the "r" (or "b", as it were).

    The other parts assures that everything else besides [BOOKS]b is a number

  11. #11
    Registered User
    Join Date
    04-27-2021
    Location
    San Francisco, CA
    MS-Off Ver
    2020
    Posts
    6

    Re: Check cell if following specific pattern

    THANK YOU! Learned something new and definitely makes sense. Thank you thank you!

  12. #12
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Check cell if following specific pattern

    Ignore... Formula did not work all the time
    Last edited by Rick Rothstein; 05-01-2021 at 01:52 AM.

+ 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. Replies: 4
    Last Post: 01-27-2020, 04:40 AM
  2. Format a cell with a specific pattern possible?
    By digace in forum Excel General
    Replies: 2
    Last Post: 05-10-2017, 02:16 PM
  3. [SOLVED] Retun the value of a cell above a specific pattern
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2015, 12:02 AM
  4. Copy down hypertext cell range pattern & cell reference formula pattern
    By Underexcelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 03:23 AM
  5. [SOLVED] Remove pattern from cells only if another cell has a specific selection from dropdown list
    By mook25 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 08-16-2013, 07:32 AM
  6. How do I set the tab stops in a specific cell pattern?
    By PE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-26-2006, 04:00 PM

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