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

Jorge Villarreal

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!

Andrée Starå

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!

Jorge Villarreal

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.