How can I convert a column with separated value items into a choice list

I have a csv that I have imported that has a column that is a semi-colon separated list (e.g. "foo ; bar")

I’m doing some cleanup on it, like removing content in parentheses. My problem is that I can’t figure out how to expand this into a choice list.

Here’s my current code:

import re
analytics = $Analytics
analytics_clean = re.sub(r'\(.*?\)', '', analytics)
analytics_list = analytics_clean.split(' ; ')
analytics_list = [item.strip() for item in analytics_list]

But when I convert this to a choice list, it doesn’t know how to interpret the values.

Hi @Jared_Scheel.

Try replacing the semi-colons with commas, and then converting the column to a Choice list.


hi @georgegevoian, thanks for the reply. I tried that, but it still doesn’t appear to pick up the values.

Screenshot 2023-11-02 at 12.04.16 PM

Had to break this up, because I couldn’t put multiple images in one message (new user).

Screenshot 2023-11-02 at 12.05.19 PM

Thanks for the screenshots.

Just to confirm: was the formula splitting the choices before, but they all appeared as invalid choices? I tried your formula with some sample data containing semi-colons and it expanded it correctly, but since no choices were configured, they all appeared as invalid.

One way to fix that is to round-trip the column from formula to data, and then back to formula. You can convert it to a data column by clicking the Formula Column dropdown under Column Behavior, and then clicking “Convert column to data”. You’ll then need to convert it from a Choice List to Text, and then back to a Choice List, which should cause it to auto-detect the individual choices in the data. Finally, you can set the column type back to being a formula (or keep it as a data column if it doesn’t need to be a formula). It’s a bit cumbersome, but a simpler way doesn’t come to mind.


Ah ok, that makes sense I think. So, I just fixed it by switching the type back to text, then applying my transformation to the columns directly, then switching to choice list. Thank you!

1 Like