Hi,
I am using Zapier to pull booking information into Airtable. The information comes in like this:
100-007 Storage boxes x 4.0 @ £4.17 = £16.68
I am wondering if I can split this information up into separate columns (column titles are in italics).
Location code = 100
Sales code = 007
Item = Storage Boxes
Quantity = 4
Price Per Item = £4.17
Total Price = £16.68
Any help would be much appreciated.
Thanks
Gal
+14Hi Simon! Welcome to the forums.
So this can be done with a bunch of Formula fields using LEFT(), RIGHT(), MID(), LEN(), and SEARCH(), but it’s pretty complicated, includes a few hidden fields, and it requires that the format of the initial string always stay the same.
If it’s possible to separate these fields before they get to Zapier, or in the Zapier steps, I personally think that would be a better way to do this.
If not though, here are some steps to get you started:
Here’s a formula for Location Code, assuming the field it is starting from is called “Name”:
LEFT(Name, SEARCH("-", Name) - 1)
Once you have that field, you can calculate Sales Code from it(note that this requires the sales code to always be 3 digits):
MID(Name, SEARCH({Location Code}, Name) + LEN({Location Code}) + 1, 3)
After that it’s a little tougher. The only separator after the Item name is an “x”, but we can’t perform a regular search for this because the Item name itself might have an “x” in it (like it does with “Storage Boxes”). You’ll run into something similar with the pricing info, since there are two "£"s found in the string.
My best solution for this would be to create some additional fields, with formulas to slice the main string into more manageable pieces. Then performing calculations on these sliced strings seems like it would be more manageable.
This could also be accomplished with scripting. Javascript has a “last index of” method, which could help parse the price data. It’s discussed here:
Let me know if what you decide, I may be able to help more. But the remaining steps for creating all of the formula fields are a little complex for a single forum reply. Hope this gets you set on the right path at least though.
Hi Simon! Welcome to the forums.
So this can be done with a bunch of Formula fields using LEFT(), RIGHT(), MID(), LEN(), and SEARCH(), but it’s pretty complicated, includes a few hidden fields, and it requires that the format of the initial string always stay the same.
If it’s possible to separate these fields before they get to Zapier, or in the Zapier steps, I personally think that would be a better way to do this.
If not though, here are some steps to get you started:
Here’s a formula for Location Code, assuming the field it is starting from is called “Name”:
LEFT(Name, SEARCH("-", Name) - 1)
Once you have that field, you can calculate Sales Code from it(note that this requires the sales code to always be 3 digits):
MID(Name, SEARCH({Location Code}, Name) + LEN({Location Code}) + 1, 3)
After that it’s a little tougher. The only separator after the Item name is an “x”, but we can’t perform a regular search for this because the Item name itself might have an “x” in it (like it does with “Storage Boxes”). You’ll run into something similar with the pricing info, since there are two "£"s found in the string.
My best solution for this would be to create some additional fields, with formulas to slice the main string into more manageable pieces. Then performing calculations on these sliced strings seems like it would be more manageable.
This could also be accomplished with scripting. Javascript has a “last index of” method, which could help parse the price data. It’s discussed here:
Let me know if what you decide, I may be able to help more. But the remaining steps for creating all of the formula fields are a little complex for a single forum reply. Hope this gets you set on the right path at least though.
Thanks, I will give this a go!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.