Create a "split" string function for formulas

Google sheets already supports this, whereas Excel does not. Airtable would be wise to follow Google’s lead. Or leapfrog them both and add a ton of array functionality.

11 Likes

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.

18 Likes