Extract Series of Occurrences of Strings into Separate Columns

Options

Hi @Paul Newcome ,

You mentioned in another thread here:

https://community.smartsheet.com/discussion/63606/extracting-string-from-a-cell?

"If you wanted to parse them out across individual columns, we would instead use a series of LEFT/FIND and MID/FIND statements."

The previous thread looked to me like someone was using the Jira Connector and separated linked issues by type of relationship, to sort of remove "excess" relationships. I need to do the same thing, but involving many-to-many issue links, such that my Smartsheet Issue Links column cell would look like this:

"relates to xyz-9992, relates to xyz-9991, tests xyz-0001, tests xyz-0002"

but maybe the strings come in like the above, maybe they're mixed up like ...

"tests xyz-0002, relates to xyz-9991, tests xyz-0001, relates to xyz-9992"

Some of my tickets will have one relationship. Some will have many. When there are many, I think I need to do what you mentioned of parsing each out into an individual column, based on its relationship like this:

Relates to 1 | Relates to 2 | Tests 1 | Tests 2

Do you think this would be possible? I'm not sure how to find the "first" and "second" instances of "tests" or "relates to" and so on.

Thanks

UWFT

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @uwft

    There isn't a direct formula that would parse out these options. You could bring the values into a multi-select cell to separate the values, but they would still appear in one cell:

    =SUBSTITUTE([Column Name]@row, ",", CHAR(10))


    Will you always have the same number of characters per "relates" and "tests"? Do you have a set maximum number of values that could be in this one cell?

    What I would do is first strip out the pre-text to the numbers so that you can have a consistent number of characters per value:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Original Data]@row, "relates to", "R-"), "tests", "T-"), ",", CHAR(10))


    Then you can look into this helper column and with a Nested IF statement pull out the first instance of an R - value:

    =IF(LEFT([Substitute Formula]@row, 1) = "R", LEFT([Substitute Formula]@row, 11), IF(MID([Substitute Formula]@row, 14, 1) = "R", MID([Substitute Formula]@row, 14, 11), IF(MID([Substitute Formula]@row, 27, 1) = "R", MID([Substitute Formula]@row, 27, 11))))


    However I've only built this formula to look up to the 4th value to search for an R. If you have 10+ "tests" that could possibly appear before the first "Relates to" this could end up being quite lengthy.

    Let me know if this points you in the right direction!

    Cheers,

    Genevieve

  • uwft
    uwft ✭✭
    Options

    Thank you, @Genevieve P. !

    I'm not sure exactly how I may apply this but, yes, I think this points me in the direction. The multiselect helper is gold by itself.

    I cannot set a maximum number of relationships at the moment, but ... maybe we need a policy.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!