I have a column called Investors and it's a Text type. It's a comma separated string. "Foo, Bar, Baz". I'm trying to loop through the string and write each value to a new table. I can't figure out how to do this. Any ideas?
To split your comma-separated Investors column and write each value to a new table:
1. Use the "Extract Data From Values" action on your Investors column
2. Add the column using the Forward Slash (/) command
3. Apply this custom extraction Regex: [^, "]+"?[^, "]+
4. This will convert your comma-separated string into a list
5. Then use "Write to Table" to send this list to your destination table
When configuring Write to Table, select your list column and map the individual values to the appropriate columns in your destination table.
Is there a way to use a formula field to return a list?
Yes, you can use a formula field to return a list. Add a new Formula column, then use the AI Formula Generator to create a formula that extracts your comma-separated values into a list. For your Investors column, you could prompt: "Convert the comma-separated string in /Investors into a list" This is particularly useful when you need to reference entire lists of data or when you want to later use "Write to Table" to move each list item into its own row.
The formula field is returning a stringified array - I need an actual list.
To get an actual list instead of a stringified array, use the "Extract Data From Values" action:
1. Select your Investors column
2. Use the "Extract Data From Values" action
3. Add the column using the Forward Slash (/) command
4. Apply this custom extraction Regex: [^, "]+"?[^, "]+
This will properly split your comma-separated values into a usable list that you can then use with "write to table" or other list operations.
Here's the string I'm working with. I need to capture everything inside the comma (including spaces) Blumberg Capital, Exponent, Aspenwood Ventures, HighSage Ventures, Newlands
To split your comma-separated string into a proper list:
1. Use the Extract Data From Values action on your column
2. Add the column using the Forward Slash (/) command
3. Apply this custom extraction Regex: [^, "]+"?[^, "]+
This will properly split your string "Blumberg Capital, Exponent, Aspenwood Ventures, HighSage Ventures, Newlands" into individual list items that you can use with write to table.
Can you pass a function like Split() instead of a regex?
Based on the available documentation, Clay's "Extract Data From Values" feature uses regex patterns rather than direct Split() functions. For your comma-separated investor list, the recommended approach is still using the regex pattern [^, "]+"?[^, "]+
which will properly split your values including spaces.