Is there a way to create a list out of joined string in a cell?

Options
Jorge Villarreal
Jorge Villarreal Employee
edited 09/08/21 in Formulas and Functions

I have Sheet A, were I have a column with Task Description that has parents. I created another column where I put the parents corresponding to the child.

Example:

Parent ID .........|........Task Description

[blankcell]........|........Parent 1

[Parent 1].........|............Task 1

[Parent 1].........|............Task 2

[blankcell]........|........Parent 2

[Parent 2].........|............Task 1

[Parent 2].........|............Task 2

On Sheet B I use =JOIN(DISTINCT({Parent ID}), ",") where I get all unique values from the Parent ID column on Sheet A as a string:

[Parent 1],[Parent 2]

What I want to do now is separate those parents into a list so that I can use the information for other metric related formulas. That is where I'm stuck, I don't know how to "unjoin" a string of text inside of a cell.

The goal is to get the names of parents (no duplicates) from Sheet A to Sheet B so that I can use them for formulas. This has to be automated, if there's manual work (adjusting ranges, fixing formulas every week, etc.) then this won't do.


Thanks for taking your time and hope you have a great day!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Jorge Villarreal,

    I hope you're well and safe!

    Try something like this.

    Add a closing , to your text, so it looks like this

    text,text,text,

    I've also named the example columns as follows.

    The one with the text is named Text, and the others are named A, B, C >

    • Add the following formula to column A
    =LEFT(Text@row, FIND(",", Text@row) - 1)
    
    • Add the following formula to column B and drag the formula across the rest of the columns on the same row.
    =LEFT(SUBSTITUTE($Text@row, JOIN($A@row:A@row, ",") + ",", ""), 
    FIND(",", SUBSTITUTE($Text@row, JOIN($A@row:A@row, ",") + ",", "")) - 1)
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jorge Villarreal
    Options

    Hi Andrée

    First of all, thanks for the quick reply.

    Unfortunately, this does not work. When going beyond the 2nd parent, the formula stops working. It throws the error "#Invalid Value". I honestly don't know why.

    I did a correction to the formula and got the result. I don't know how, honestly. I figured that the structure was ok, maybe a typo was wrong? So I started fiddling with the $ signs to see if I got what I needed.

    Fortunately, I got it. But, with the problem that values are now being repeated (example): Parent 1, Parent 2, Parent 3, Parent 1, Parent 1, etc (all on separate columns, which is great).

    What I did now is I nested the formula inside an If statement saying that if the value calculated is = to the value from $text@row (the first column), then display "Stop here", if not, continue with the calculation.

    The correction I made was here (look for the crossed bold $ sign):

    =LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN($B@row:B@row, ",") + ",", "")) - 1))

    As you can see, I deleted the $ sign from the second round of the join formula.

    The final result with the if statement is:

    =IF(LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN(B@row:B@row, ",") + ",", "")) - 1) = $A@row, "Stop here", LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN(B@row:B@row, ",") + ",", "")) - 1))

    As of right now this is working! If you see a flaw with something I might have done, please contact me, I would greatly appreciate it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!