Text to different Rows - Split by delimiter.

prathap.krishnashetty69881 · 02/24/20

Hi,

I have a[email protected]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 · 02/24/20

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

Paul Newcome · 02/24/20

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 · 02/24/20

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

prathap.krishnashetty69881 · 02/24/20

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

Paul Newcome · 02/24/20

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

prathap.krishnashetty69881 · 02/24/20

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

Paul Newcome · 02/24/20

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 · 10/11/21

@Paul Newcome嗨,我也试图解析逗号环保总局的列表rated 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 · 10/12/21

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

Rob C · 04/29/22

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. · 08/19/22

Hi@Paul Newcome

我也有同样的问题@prathap.krishnashetty69881. Could you, please!,分享sheet again?

PeggyLang · 08/25/22

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

NikkiOno · 09/01/22

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

mniner · 09/26/22

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

Margaret Walker · 10/22/22

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

Paul Newcome · 10/24/22

@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), "")) >=[email protected], MID([Assigned To]# + CHAR(10), FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~",[email protected]- 1)) + 1, FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~",[email protected])) - (FIND("~", SUBSTITUTE([Assigned To]# + CHAR(10), CHAR(10), "~",[email protected]- 1)) + 1)), ""), "")

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

Barbara T. · 11/30/22

Hi@Paul Newcomeet.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.

Text to different Rows - Split by delimiter.

Best Answers

Answers

Categories