How to sum a string of comma-separated dollar values

I am bringing in refund data from Shopify via an automation with webhook. When more than one item is returned, the webhook sends all of the subtotal values in a comma-separated list (see screenshot below). I’m trying to put this into a currency field in an automation, but that is causing the automation to fail when there is more than one item in the list. If I change the field type to single line text, the automation will work, but then I have to find a way to sum these values in a different field. The list of values could be one or many (up to 5+). Do you know of a formula that can accomplish summing the values in the string or do I have to have a script?
Screen Shot 2022-11-21 at 11.17.50 AM
Thanks for all the help in advance!

Airtable is not great at handling this within tables.
Since you are already using an automation, I’d suggest adding a script action in your automation and building a small script that can split the string from Shopify (using String.split()), cast the values as numbers, and then add them up.

Then you can take the output of your script action and put that into a Currency field.

2 Likes