Text to columns in Smartsheet

I have a cell with multiple values separated by commas. (eg. A, B, C, etc.)
I want to try get these separate values in a column:
eg. Row 1 - A
Row 2 - B
Row 3 - C
and so on..
PS. There is no limitation on how many values this cell has. It can vary from no values to up to 40 values.
Answers
-
Is this something that is needed over and over again such as with form entries, or are you looking for something more like a one time solution? Are you able to provide more details as to the flow of data and how you intend to use it once split?
-
Hi Paul,
Thanks for your response. I am looking for a one-time solution which will automatically keep updating as changes are made to the main sheet.
I am using the main sheet and a Dashboard sheet (which will be used to summarize data for the Dashboard).
In the main sheet, there are various regions in a column (which can be repeated multiple times).
In the Dashboard sheet, I have used the below formula to summarise all regions in one cell separated by commas:
=JOIN(DISTINCT(COLLECT({ColumbiaScope_ClassIII_BU}, {ColumbiaScope_ClassIII_BU}, @cell <> "")), ", ")
Now, my aim is to populate these regions in a column automatically. I cannot use LEFT or RIGHT functions since they only really work for a specific number of values. Would you have any recommendations?
Appreciate your help!
-
Are you able to provide a screenshot of a mock-up for context?
-
Yes ofcourse.. For example, this is the main sheet with all the data:
And this is the Dashboard sheet which will feed into the Dashboard. Cell B2 has the formula: =JOIN(DISTINCT(COLLECT({Main Sheet_BU}, {Main Sheet_BU}, @cell <> "")), ", ")
I am looking for cell B2 values to be populated in Column A under BU Title.
Note: This field is not limited to only 4 values.. it can vary from none to 40.
Also to make sure, if any new rows are added in the Main Sheet, we want the cell B2 in Dashboard sheet and the column A under BU Title to be populated automatically.
-
For this you would need a parsing solution. There are a number of parsing solutions already out there in the Community, but my personal preference is the one that involves the SUBSTITUTE function.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!