Is there a way to create a list out of joined string in a cell?
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
-
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.
-
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
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!