Split() formula -- remedy? Need to split cell string at semicolons

[Rose_Pember]

Hello,
I have a form response that pulls data in this format (bucket number, bucket weight;)
##, ##.#; ##, ##.#
for example, 15, 32.5; 18, 33.4; 6, 35.1

I need to split the data so that I have the second string numbers added up to a total weight. I understand I need to split (in excel) the text at the semi colons, and then extract the last 3 characters to compound.

Any ideas how to structure or accomplish this in airtable?

thanks so much.
Rose

[kuovonne]

Welcome to the Airtable community!

I’m afraid that Airtable does not have a split function.

If you have a lot of time, you can use this workaround for splitting out information across different fields, as long as you have less than 100 buckets. You could then use string handling functions to extract the weight for each bucket, VALUE to turn the text string into a number, and then another formula field to sum the weights.

Another option is to use a script to parse the information and calculate the total weight. For example, you could have an automation script that calculates the total weight when a new record is submitted. (If you want a script but do not code, and have budget to hire someone, feel free to book an appointment with me to discuss your needs.)

If you can change the form where the data is being entered to separate out the data more, there might be other options.