The main problem is that a column formula will only extend to populated rows. You could technically add another column which is just a list of number (1,2,3, etc, as many as you need). Then you could use a formula like this:
=INDEX(COLLECT([Col15]:[Column18], [Col15]:[Column18], NOT(ISBLANK(@cell))), [NumberedRowColumn]@row)
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!