Parsing a delimited string

[WilliamPorterTech]

I need to parse out a string that looks like this:

Cli/Proj/Agent/$125

In case it’s not obvious, that’s client/project/agent/$rate. This comes from a field named “ClientProjAgentRate_”.

Getting the rate is fairly easy, because the rate’s at the end of the string, and it follows a dollar sign. There should only ever be 1 dollar sign in the string, so this works:

RIGHT(
CliProjAgentRate_,
LEN( CliProjAgentRate_ ) - FIND( "$", CliProjAgentRate_ )
)

But finding the agent in particular is rather harder. In FileMaker there’s a function POSITION() that more or less corresponds to Airtable’s FIND() – but there’s one important difference. The FileMaker POSITION() function allows me to indicate which occurrence of the search string I am interested in. So if the Airtable function worked like the FileMaker one, I could write

FIND ( “/”, CliProjAgentRate_, 1, 2 )

In other words, start from the beginning of the string (“1”) and find the SECOND occurrence of the slash (“2”). That second slash will always precede the agent value. I could then use the same function but change the occurrence to 3, drop these into a MID() function, and get the agent value.

And that’s basically what I’m doing in Airtable. Unfortunately Airtable’s FIND() doesn’t have that extra “occurrence” parameter. So it looks to me like I have to nest a series of FINDs, adding +1 to the result of each one, to get to the third slash. In other words,

locate the slash that starts
after the location of the slash that starts
after the location of the first slash.

Which ends up looking like this:

I’m glad I only have three slashes to deal with. And of course, this doesn’t PARSE the string. This just gets me the address of the third slash. Now I have to plug this bit and the bit that gets me the position of the second slash into a MID function.

Please tell me I’m missing something or that there’s a better way to do this! Thanks in advance.

William

p.s. I can think of an alternative, viz., using different delimiters, say ! instead of the first slash and @ instead of the second slash, and use $ alone in lieu of the third slash. But I’m wondering if there is a better way to do this job without changing the delimiters.

[Zollie]

Would the scripting block work instead? I’ve given up on formulas for complex parsing (unless my formula product suggestion ever gets picked up).

[WilliamPorterTech]

@Zollie,

Thanks for the suggestion. I haven’t started looking at the scripting block yet but may soon.

But providing a Split() function is an awesome idea. Actually, although I use Google Sheets pretty often, I was unaware of this function. It’s brilliant and yes, would make things massively simpler in Airtable.

Who do I bribe to get this implemented?

I wonder sometimes if the Airtable staff isn’t spending so much time on customer support (necessary because of Airtable’s limitations) that it doesn’t have time to implement the improvements to reduce that customer support traffic. It’s a brilliant product and obviously the folks that work here must be brilliant. I can only conclude that they’re very, very busy.

.

That said, in my case, things could be improved by an even simpler change implied in my post: give the FIND() function an optional fourth parameter for occurrence.

William

[Bill.French]

@WilliamPorterTech

Who do I bribe to get this implemented?

Indeed, but I suspect many have attempted to bribe them since 2016 to enhance the parsing experience.

[WilliamPorterTech]

I “liked” both your post and Zollie’s. But I’m not sure that’s same thing as voting for a feature request. Where’s the feature request?

William

[Bill.French]

@WilliamPorterTech

That said, in my case, things could be improved by an even simpler change implied in my post: give the FIND() function an optional fourth parameter for occurrence.

This change would indeed improve FIND(), but locating positions within strings is only part of the problem. Here’s why - pick out the FIND() functions in this example and try to determine how much better this parser would be with your new feature. It’s not going to be much better because useful parsing requires the tokenization of strings in arrays. Locating character instances is but one part of the problem.

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)) )

Unfortunately, customers have been beating up Airtable with requests for slight incremental improvements for the better part of half a decade; it is death by a thousand cuts. Instead, they should look at the business requirement, not the functional requirement, and provide a way for all parsing use cases to be addressed. Split() makes it possible to build all sorts of parsing solutions and until that is provided, only small slices of users will be satisfied with incremental improvements.

In my view, let’s not ask for a better FIND(); instead, ask for that which already exists in string-handling for javascript. It’s well-tested, broadly understood, and fully vetted to ensure just about anything imaginable can be achieved.

@WilliamPorterTech

But I’m not sure that’s same thing as voting for a feature request.

The broad user community of Airtable doesn’t come from a javascript or programming background. Split() is largely a foreign idea to most people because it also requires a deep understanding of arrays, yet another fleeting aspect for business domain experts who simply want to manage their data better. The bad news about the Split() function and the power of arrays is that nobody knows about the Split() function or how to use arrays.

Airtable must listen carefully to feature requests and build features that help users help themselves. In some cases, they need to educate users who can drastically improve their data work using these typically new and powerful approaches.

[Zollie]

I’m not sure that’s same thing as voting for a feature request. Where’s the feature request?

I consider most posts tagged with ‘product suggestion’ feature requests. And by ‘feature request’ I mean, someone is requesting additional functionality from Airtable. The more visibility a post gets on this community, the more likely Airtable staff takes notice. So the upvote helps. Many thanks.

[Bill.French]

@Zollie

And by ‘feature request’ I mean, someone is requesting additional functionality from Airtable.

Yes, I agree. A good idea for a feature doesn’t have to be labeled as a feature request. I’m pretty sure the wisdom of crowds make their needs apparent to Airtable through a variety of metrics of which, up-voting is just one.

One metric is the number of words written about parsing and I suspect this metric alone makes it an obvious need.