Extract Series of Occurrences of Strings into Separate Columns
Hi @Paul Newcome ,
You mentioned in another thread here:
https://community.smartsheet.com/discussion/63606/extractingstringfromacell?
"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 manytomany issue links, such that my Smartsheet Issue Links column cell would look like this:
"relates to xyz9992, relates to xyz9991, tests xyz0001, tests xyz0002"
but maybe the strings come in like the above, maybe they're mixed up like ...
"tests xyz0002, relates to xyz9991, tests xyz0001, relates to xyz9992"
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
Answers

Hi @uwft
There isn't a direct formula that would parse out these options. You could bring the values into a multiselect 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 pretext 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

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
Categories
Check out the Formula Handbook template!