Extract Series of Occurrences of Strings into Separate Columns

uwft

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

Genevieve P.

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

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.