Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formatting Text

1306 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_Gallagher
5 - Automation Enthusiast
5 - Automation Enthusiast

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

2 Replies 2
Nick_Dennis
7 - App Architect
7 - App Architect

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.

Thanks, I will give this a go!