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

  • Genevieve P.
    Genevieve P. Employee
    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

  • Genevieve P.
    Genevieve P. Employee
    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

  • It is unfortunate that I cannot automate the process, but I very much appreciate your insight and helpful response!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!