+9I 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")Best answer by kuovonne
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"
)
))
+29The 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"
)
))
+9The 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"
)
))
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.