Transpose stacked cell values into separate columns

BrianDN

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

Kelly Moore

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

BrianDN

Hi Kelly,

Yes, WOW, this is exactly what I need. This splits everything at carriage enter.

Many thanks for this

BrianDN

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?

BrianDN

link to answer provided by Lucas Rayala

https://community.smartsheet.com/discussion/comment/364616#Comment_364616