Text to different Rows - Split by delimiter.

Paul Newcome

@Barbara T. Are you able to provide some screenshots for reference?

Barbara T.

Yes!

This is what I've been working on (and struggling with lol).

Paul Newcome

@Barbara T. It looks like you just need a "-1" after the find function to get rid of the commas.

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

That second formula that is throwing the error... It looks like you have some misplaced parenthesis. You closed out the RIGHT function too early and forgot to close the LEN function after the cell reference. If you take that closing parenthesis after the first [Primary Column]1 and move it to after the second, you should get rid of that error.

Barbara T.

@Paul Newcome The first solution worked!

I can't quite get the "=Right" to work though. Here's another screenshot.

Much appreciated!

Paul Newcome

@Barbara T. It looks like you changed the cell reference in the FIND function to [Column2]. Now that we have removed the comma from that cell, the FIND function outputs a zero. LEN minus zero equals LEN which means we are telling it to basically pull everything. If you search for the comma in the [Primary Column] instead, you should be good to go.

Barbara T.

Thank you, @Paul Newcome ! I got it to work!

sharing completed code for folks to reference in the future!

Paul Newcome

@Barbara T. Happy to help. 👍️

dylanchaney

@Paul Newcome I couldn't get your formula to work, but I did find a manual solution for a string with 14 data values to be split, for anyone who's willing to spend an extra couple minutes typing. Can you look this over and tell me if you can identify any opportunities to make this more amenable to a column formula, or a more easily copied formula?

The string I wanted to split was a list of Building Names (joined from a column on a different sheet) with 10 or more construction projects associated with them. This was stored in my building name column, titled [BName]. There were 14 total values joined into the string below in the first row, [BName]1. *The names of these buildings have been changed from their original value for discretion*

"Mission Control-Eiffel Tower-Statue of Liberty-Arc de Triomphe-Empire State Building-Sydney Opera House-The White House-Seattle Space Needle-Taipei 101-Fallingwater-Rock Hall-Hard Rock Cafe-Buckingham Palace-Varied"

Since I kept the entire process in one column, my first formula, in [BName]2, is simple:

=IFERROR(LEFT(BName$1, FIND("-", BName$1) - 1), "")

This returns my first desired value, "Mission Control", without the "-" that follows. Note: I use absolute references to all cells so I can copy and paste my formula quickly and easily.

My next formula in [BName]3 is:

=IFERROR(MID(BName$1, LEN(BName$2) + 2, FIND("-", BName$1, LEN(BName$2) + 2) - FIND("-", BName$1, LEN(BName$2) + 1) - 1), "")

This returns my second desired value, "Eiffel Tower", again without the "-". I then copy and paste [BName]3 into [BName]4.

=IFERROR(MID(BName$1, LEN(BName$2 + BName$3) + 3, FIND("-", BName$1, LEN(BName$2 + BName$3) + 3) - FIND("-", BName$1, LEN(BName$2 + BName$3) + 2) - 1), "")

Once I added/changed the 6 elements, shown above in bold/italics, to the copied formula, it returns my third desired value, "Statue of Liberty".

This was repeated for all 14 values until I had each one listed out in a separate cell. However, because the logic will always fail for the last string, the last formula had to be:

=IFERROR(RIGHT(BName$1, LEN(BName$1) - (LEN(BName$2 + BName$3 + BName$4 + BName$5 + BName$6 + BName$7 + BName$8 + BName$9 + BName$10 + BName$11 + BName$12 + BName$13 + BName$14) + 13)), "")

This returned my last desired value with no errors. Also, if I ever changed my original string in [BName]1, these cells would reflect the changes, so long as they're still delimited by a dash, "-". It took an extra couple of minutes to copy/paste and add the 6 additive elements but I was able to establish a column that I'll only have to add on to for my scope of work if there happen to be more buildings down the road with more than 10 construction projects associated with them.

Paul Newcome

@dylanchaney There are a number of parsing solutions that could work and a much more user friendly. The right one for you depends on your exact needs.

If you pull your list into a Sheet Summary field (called "Building Name" in this example), you can use a SUBSTITUTE method as a column formula with a helper column that is manually populated with numbers to parse out your list.

Manual column is a text number (called "Number" in this example) and you would manually enter the numbers 1 through however many you think you will need. You can also add a buffer with this method because unused spaces will just remain blank in case there are more eventually added to it.

Then in the column where you want your list you would use

=IFERROR(MID("-" + [Building Name]# + "-", FIND("!", SUBSTITUTE("-" + [Building Name]# + "-", "-", "!", Number@row)) + 1, FIND("!", SUBSTITUTE("-" + [Building Name]# + "-", "-", "!", Number@row + 1)) - (FIND("!", SUBSTITUTE("-" + [Building Name]# + "-", "-", "!", Number@row)) + 1)), "")

dylanchaney

@Paul Newcome Oh interesting, I didn't even think to use a summary field, I was limiting my data to columns. I see the logic now, thank you!