Transpose stacked cell values into separate columns
Hello,
Is there a way to split a stacked cell values into multiple columns with formulas?
So Col6 is the starting data that is dynamic, so it will change as forms are submitted so sometimes the cells have one name, sometimes they have 5.
I'm hoping the output would look like the below
Thanks for any help with this
Answers
-
Hey @BrianDN
Try these
Col7
=IF(FIND(CHAR(10), [Col6]@row) = 0, [Col6]@row, IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) >= 1, LEFT([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)))))
Col8
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 1, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 1, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)))))
Col9
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 2, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2) + 1), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 2, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)))))
Col10
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 3, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 3, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 4)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)))))
Col11
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 4, RIGHT([Col6]@row, LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 4))))
Will these work for you?
Kelly
-
Hi Kelly,
Yes, WOW, this is exactly what I need. This splits everything at carriage enter.
Many thanks for this
-
Hi Kelly,
To piggy-back on this question, is there a "correct" way to append columns Col7-Col11?
So that the end result would look like this:
I would achieve this in excel via power query, but the only way I know how to do this in SmartSheet is essentially manually.
Or should I post this as a new topic question?
-
link to answer provided by Lucas Rayala
Help Article Resources
Categories
Check out the Formula Handbook template!
