Contains an Exact Match

Options
Paul H
Paul H ✭✭✭✭✭✭
edited 03/04/22 in Formulas and Functions

Problem is double results using the contains function

=JOIN(COLLECT({ECN Num}, {ECN Item}, CONTAINS(Item@row, @cell)), " ")

{ECN Item} cell is a list of affected items numbers

If the ECN Item I am looking for is "1234" I also get the results for item number "1234-05"

Any advice?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    I didn't realize you could have multiple entries in a single cell. Sorry about that. it looks like you are not using a multi-select dropdown, so will it always be a line break immediately after the 1234 if it is strictly that and you want to exclude those that have a hyphen?


    If you can rely on having a line break after 1234 in those that you want to grab, you can include that in your CONTAINS function.


    =JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS(Item@row + CHAR(10), @cell)), " ")


    Basically this is looking for "1234_line_break" which will exclude "1234_hyphen"

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try something like this...

    =JOIN(COLLECT({ECN Num}, {ECN Item}, @cell = Item@row), " ")

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    The {ECN Item} cell can contain from one to twenty items, if I used "@cell = Item@row" it would skip any cell with multiple entries wouldn't it?

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    I made a mini test sheet rather than the big crossref database, I am trying to pick out the only the ECN's that affect item 1234

    This is the formula in Sheet summary

    =JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS("1234", @cell)), " ")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    I didn't realize you could have multiple entries in a single cell. Sorry about that. it looks like you are not using a multi-select dropdown, so will it always be a line break immediately after the 1234 if it is strictly that and you want to exclude those that have a hyphen?


    If you can rely on having a line break after 1234 in those that you want to grab, you can include that in your CONTAINS function.


    =JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS(Item@row + CHAR(10), @cell)), " ")


    Basically this is looking for "1234_line_break" which will exclude "1234_hyphen"

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    Thanks Paul, I might have found a away to make this work using the line break as you suggest

    Problem: Line break does not work if there is only one affected item and occasionally people have used commas, slashes, or even brackets when entering affected items via the form. With over 13k entries its too much work to go back and fix them all.

    So a helper column to clean up data and then add a final char(10) + "!" for those single items

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Affected Items]@row, ")", CHAR(10)), "(", CHAR(10)), ",", CHAR(10)), "/", CHAR(10)) + CHAR(10) + "!"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Yes. That should do the trick to swap out the invalid delimiters and also account for a single entry of 1234.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!