Text to different Rows - Split by delimiter.

prathap.krishnashetty69881

Hi,

I have a cell@row value in the column Zone1 separated by a delimiter which needs to be split into different rows as a list of values similar to column Zone1new. Could i get help on this?

Paul Newcome

THIS SHEET provides a solution for parsing a comma delimited text string down a column.

Paul Newcome

Happy to help. 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Paul Newcome

THIS SHEET provides a solution for parsing a comma delimited text string down a column.

prathap.krishnashetty69881

Hi Paul, I used the same method but unable to parse the list. Not sure, what went wrong!

Paul Newcome

Remove the VALUE function form the formulas in the List column.

prathap.krishnashetty69881

Great! thanks so much, its working now. I got my solution. Most Appreciated🙏.

Paul Newcome

Happy to help. 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Chandlerw

@Paul Newcome Hi, I am also trying to parse a list of comma separated names down a column. I had a question about your solution, how are you able to use a circular reference and not get an error in the string column?

Paul Newcome

@Chandlerw I don't have any circular references. Are you able to pinpoint exactly where the circular reference is coming from?

Rob C

Hello, I am new to smartsheets and have been looking for a way to split text in a row delimited by a "," into rows in a separate column. Unfortunately the sheet that provides a solution for parsing a comma delimited text string down a column is no longer available. Would you be able to share this sheet again @Paul Newcome ? Thanks so much. Appreciate your help!

ChristianCo.

Hi @Paul Newcome

I have the same problem as @prathap.krishnashetty69881. Could you, please! 🙏, share the sheet again?

PeggyLang

Hi @Paul Newcome seems this sheet may also answer my questions. Please reshare sheet???

NikkiOno

@Paul Newcome I have the same question. Could you pls share that sheet with me as well?

mniner

@Paul Newcome I'm trying to do this as well. Would you be willing to reshare the sheet?

Margaret Walker

@Paul Newcome I'm also struggling with this and the sheet you referenced is no longer available. Can you share the sheet again or @prathap.krishnashetty69881 can you provide the solution that he helped you with? Any guidance is appreciated. Thanks so much!

Paul Newcome

@Rob C, @ChristianCo., @peggy lang, @NikkiOno, @mniner, and @Margaret Walker

My apologies for the delayed response. Somehow I got unsubscribed from notifications on this post.

The previous solution is actually outdated as I have developed a more reliable and easier to build solution.

Step 1: Create a text/number column called "Number" and manually enter the numbers 1 through whatever to accommodate how large of a list you anticipate having (and adding extra rows for a buffer).

In the remaining steps you will need to replace "[Assigned To]#" with a reference to whatever cell holds your text string.

Step 2: Enter this formula on the first row...

=IFERROR(LEFT([Assigned To]#, FIND(CHAR(10), [Assigned To]#) - 1), [Assigned To]#)

Step 3: Enter this formula on the second row...

=IFERROR(IF(LEN([Assigned To]# + CHAR(10)) - LEN(SUBSTITUTE([Assigned To]#, CHAR(10), "")) >= Number@row, MID([Assigned To]# + CHAR(10), FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~", Number@row - 1)) + 1, FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~", Number@row)) - (FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~", Number@row - 1)) + 1)), ""), "")

Step 4: Dragfill the formula from the second row on down to the bottom of your list.

Barbara T.

Hi @Paul Newcome et.al,

This has been super helpful.

My goal is to delimit a "latitude and longitudes" column (singular) into individual latitude and longitude columns (2 columns).

The trouble I am having is that the comma separating my text continues to exist once the cell has been delimited across columns (I'm not trying to go do the columns - which is where this forum differs but I did see in @prathap.krishnashetty69881's post that their commas didn't go away either.

Any support is appreciated.