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:
Hi @ScottWorld - yes, scripting block is the way to go here. Javascript offers a “last index of” method, so you could use this in a script to populate another field with the part of the original string you want: // set the table and query let table = base.getTable("Table 1"); let results = await table.selectRecordsAsync(); // for each record in the result set for (let record of results.records) { // get the input string let str = record.getCellValue('String'); // get the index of t…
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.
