Line breaks and commas within quotes

[Kelly_Vorrasi1]

I have a large multi-select that I then need to turn into a list format. I did the usual substitute the comma for a line break but unfortunately the items in this multi-select (that I can not edit) also use commas so now it's adding commas where I don't want them. Is there a way to get the formula to avoid those commas but not the others? 

SUBSTITUTE({Plot Themes},", ","\n")

[kuovonne · Accepted solution]

The SUBSTITUTE cannot tell the difference between the comma inside a select choice and a comma between select choices.

Here are some options:

1. Use a linked record field instead of a multi-select field. Then use a rollup field that brings the values over with the formula ARRAJOIN(values, "\n"). This is my preferred choice. You loose the pretty colored pills, but you gain other abilities. 

2. Use a formula field that looks for each possible select choice and builds the new value based on hardcode values. I dislike this method because it requires hardcoding the select choices and can be a maintenance nightmare. When you use this method, you can also eliminate the extra quotation marks. This method also does not preserve the original order of the select choices (which may ore may not be what you want0).

TRIM(CONCATENATE(
  IF(
    FIND("Apocalypse, Dystopia, & The Bomb", {Plot Themes}),
    "Apocalypse, Dystopia, & The Bomb\n"
  ),  IF(
    FIND("Double Lives", {Plot Themes}),
    "Double Lives\n"
  ),
  IF(
    FIND("Weddings & Marriage", {Plot Themes}),
    "Weddings & Marriage\n"
  )
))

[Kelly_Vorrasi1]

Thank you for this! While not ideal, I think the longer formula will have to do for now until I can make some larger adjustments to the base.