Create a "split" string function for formulas

We often make product suggestions that are based on obvious needs. Here’s one that falls into the not-so-obvious category and the lack of support for @Zollie’s recommendation is evidence the community isn’t aware of the power of a Split() function.

In my view, Split() changes everything about formulas involving text and arrays.

Recently, @Abraham_Bochner desperately reached out for help to parse a collection of strings (like this) to get the values.

<ANUZ2B2KI2XTG;ANUZ2B2KI2XTG;23.93+0=23.93;Me;26.12;Ig:r#5:>

The Airtable formula to extract 23.93 from this string this is insanely complex and almost impossible to create, let alone maintain.

RIGHT( LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1), LEN(LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) - FIND(";", LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) )

With the Split() method (or function) it’s this simple:

{Data Cell}.split(";")[2].split("+")[0]

Why this suggestion is not upvoted by the vast community membership is a mystery to me because I see repeated instances of unimaginable and unsustainable formula workarounds to do what should be absolutely effortless.

The underlying code to support Split() is also simple, well-understood and tested, and easy to implement. Ergo, I also cannot understand why Airtable isn’t all over this. It’s the one function that elevates the capabilities above all when users must transform or understand their data.

The Split() and array table functionality is still something I look for in a daily basis.

It’s one of the only reasons I still use Excel (for “Text to Columns”) and Google sheets

Having this would be a huge help to those of us who are required to maintain many different kinds of data parsing formulas for different clients with slightly different needs. It’s very tedious :disappointed:

Airtable pleaseeeee address this – we will love you even more then we already do :heart:

Ooh boy, do I ever need this. I’m often dealing with JSON data, and I need to pull it apart to extract values. Writing formulas in Airtable to accomplish this is a nightmare! A split() function would make things 1000x easier.

Like seriously, here is the code I needed to write to extract a single value from a JSON string. Just ONE value!! There’s no way this is efficient on Airtable’s servers.

IF(
  ISERROR(
    LEFT(
      RIGHT(
        {Customer Fields},
        LEN({Customer Fields})
            - LEN('"company":"')
          - SEARCH('"company":"',{Customer Fields})
          + 1
      ),
      SEARCH(
        '"',
        RIGHT(
          {Customer Fields},
          LEN({Customer Fields})
              - LEN('"company":"')
            - SEARCH('"company":"',{Customer Fields})
        )
      )
    )
  ),
  "",
  LEFT(
    RIGHT(
      {Customer Fields},
      LEN({Customer Fields})
          - LEN('"company":"')
        - SEARCH('"company":"',{Customer Fields})
        + 1
    ),
    SEARCH(
      '"',
      RIGHT(
        {Customer Fields},
        LEN({Customer Fields})
            - LEN('"company":"')
          - SEARCH('"company":"',{Customer Fields})
      )
    )
  )
)

LEFT(
  RIGHT(
    {Customer Fields},
    LEN({Customer Fields})
        - LEN('"company":"')
      - SEARCH('"company":"',{Customer Fields})
      + 1
  ),
  SEARCH(
    '"',
    RIGHT(
      {Customer Fields},
      LEN({Customer Fields})
          - LEN('"company":"')
        - SEARCH('"company":"',{Customer Fields})
    )
  )
)

IF(
  ISERROR(
    LEFT(
      RIGHT(
        {Customer Fields},
        LEN({Customer Fields})
            - LEN('"company":"')
          - SEARCH('"company":"',{Customer Fields})
          + 1
      ),
      SEARCH(
        '"',
        RIGHT(
          {Customer Fields},
          LEN({Customer Fields})
              - LEN('"company":"')
            - SEARCH('"company":"',{Customer Fields})
        )
      )
    )
  ),
  "",
  LEFT(
    RIGHT(
      {Customer Fields},
      LEN({Customer Fields})
          - LEN('"company":"')
        - SEARCH('"company":"',{Customer Fields})
        + 1
    ),
    SEARCH(
      '"',
      RIGHT(
        {Customer Fields},
        LEN({Customer Fields})
            - LEN('"company":"')
          - SEARCH('"company":"',{Customer Fields})
      )
    )
  )
)

LEFT(
  RIGHT(
    {Customer Fields},
    LEN({Customer Fields})
        - LEN('"company":"')
      - SEARCH('"company":"',{Customer Fields})
      + 1
  ),
  SEARCH(
    '"',
    RIGHT(
      {Customer Fields},
      LEN({Customer Fields})
          - LEN('"company":"')
        - SEARCH('"company":"',{Customer Fields})
    )
  )
)

I’m torn between tapping the “like” for your post and writing a deep indictment reflecting years of warnings concerning string parsing.

Your formula underscores many such examples that I have posted here over the years and despite this, the insanity continues and other features which have minimal impact continue to get attention. I have called out to the new product manager a number of these cases which have occurred in the triple-digits since 2016. We see approximately one frustrating message like this a day on average. I suspect there are many frustrations that go unstated.

This pattern demonstrates rudderless product management - where else should users place the blame?

Thoughts @Taylor_Savage?

Workaround Remedy

Build a script block that retrieves the JSON data and use javascript’s JSON.parser() method to tease out the data you need. This is the only reasonable approach and sadly requires code in this largely codeless environment.

Agreed about everything you’ve said. Airtable is a powerful product, which is why I keep coming back to it, but limitations like this are a constant frustration to me.

I had no idea script blocks were a thing. I’m an experienced Javascript programmer, so this could potentially make things easier for me. I’ll have to look into this, thanks for the tip! Do script blocks need to be manually triggered or can they be scheduled/automated/auto-triggered?

Script Blocks are manually triggered, whereas Script Actions can be automated and based on events to a degree.