Parsing Text into Child Rows
Good morning. I am running the Jira Integration, and I would like to create a view or report that lists linked issues (in the 3rd column from the screenshot) as child rows.
The text will always be in the format ("includes OR-X") but I have several problems parsing the text.
- The key number (ex: 2437) could have up to 6 character places
- There can be any number of included tasks listed in the column
- The last linked issue will not have a comma.
I would appreciate any help the community could be in helping me program this!!
Thanks much,
Adam
Best Answer
-
Hi @ARosen
There currently isn't a formula in Smartsheet that can automatically parse out pieces of data from a text string and turn those into additional rows. You would need to manually create the additional child rows and copy/paste in the data (to evaluate how many rows are needed, and where the hierarchy should be placed).
If you're just looking to surface rows that have linked issues, you could create a Filter looking at that third column.
Another option would be to have a multi-select column next to the text column, then use the SUBSTITUTE Function to replace any instance of a comma with CHAR(10) (a line break) so you have separate ORs listed, just as numbers:
=SUBSTITUTE(SUBSTITUTE([Column 3]@row, ",", CHAR(10)), "Includes", "")
Cheers,
Genevieve
Answers
-
Hi @ARosen
There currently isn't a formula in Smartsheet that can automatically parse out pieces of data from a text string and turn those into additional rows. You would need to manually create the additional child rows and copy/paste in the data (to evaluate how many rows are needed, and where the hierarchy should be placed).
If you're just looking to surface rows that have linked issues, you could create a Filter looking at that third column.
Another option would be to have a multi-select column next to the text column, then use the SUBSTITUTE Function to replace any instance of a comma with CHAR(10) (a line break) so you have separate ORs listed, just as numbers:
=SUBSTITUTE(SUBSTITUTE([Column 3]@row, ",", CHAR(10)), "Includes", "")
Cheers,
Genevieve
-
It is unfortunate that I cannot automate the process, but I very much appreciate your insight and helpful response!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!