Text to different Rows - Split by delimiter.
Hi,
I have a[email protected]值列Zone1由一个分隔符分开which needs to be split into different rows as a list of values similar to column Zone1new. Could i get help on this?
Best Answers
-
Paul Newcome ✭✭✭✭✭✭
THIS SHEETprovides a solution for parsing a comma delimited text string down a column.
thinkspi.com
-
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.
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
THIS SHEETprovides a solution for parsing a comma delimited text string down a column.
thinkspi.com
-
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.
thinkspi.com
-
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.
thinkspi.com
-
@Paul NewcomeHi, 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 ✭✭✭✭✭✭
@ChandlerwI don't have any circular references. Are you able to pinpoint exactly where the circular reference is coming from?
thinkspi.com
-
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!
-
我也有同样的问题@prathap.krishnashetty69881. Could you, please!,分享sheet again?
-
PeggyLang ✭✭✭✭
Hi@Paul Newcomeseems this sheet may also answer my questions. Please reshare sheet???
-
NikkiOno ✭
@Paul NewcomeI have the same question. Could you pls share that sheet with me as well?
-
mniner ✭✭
@Paul NewcomeI'm trying to do this as well. Would you be willing to reshare the sheet?
-
@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 ✭✭✭✭✭✭
@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.
thinkspi.com
-
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.
Categories
@Shawn_K2<\/a> UGH I make a mistake. I copied your formula without looking at it close enough 😜<\/span><\/p>
try =COUNTIFS({CPR Request Type}, HAS(@cell, \"VAVE\"))<\/p>"},{"commentID":382446,"body":"
Thanks @topazfae<\/a> for your time and efforts and I'm happy to announce that @Genevieve P.<\/a> solved this challenge 😃<\/span> - <\/p>