Text to columns in Smartsheet

SS_98

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.

Paul Newcome

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?

SS_98

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!

Paul Newcome

Are you able to provide a screenshot of a mock-up for context?

SS_98

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.

Paul Newcome

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.