It has come to light that the VALUE() function is broken.
The VALUE() function is supposed to extract numbers from a string. It’s designed to be used as a simple filtering tool to remove non-numbers from a field. This is how it is documented on the formula field reference page.
Instead, the VALUE() function does at least 2 completely different things:
The VALUE() function converts certain letters into numbers, and then multiplies those numbers by the closest numbers that it can find! This issue is documented here:
The VALUE() function interprets certain symbols as operators to perform math on all the extracted numbers from a string. This issue is documented here:
We do not know what the complete list of letters & symbols are that cause the VALUE() function to fail, so there is no telling how many different ways this function can fail.
In my opinion, everybody should avoid the VALUE() function. Or at the very least, proceed cautiously when using it.
I don’t think it needs to be avoided, nor do I feel that it’s broken. It’s just not documented clearly, and therefore is behaving in a way that some don’t expect, but I have a hunch that this unexpected behavior is still by design.
The situation where VALUE() interprets things like MB, TB, KB, etc. has been known for a while…at least by some of us. I discovered it quite a while ago and mentioned it in a thread (if I find the thread, I’ll link it here). As for the second example, I haven’t seen that before.
If the function is changed to remove these side-case behaviors, there’s a chance that some users’ formulas will break because they’re actually using these features to their benefit.
Instead of being fearful of this thing, I would prefer that we be informed. These “extra” features of VALUE() should be documented, not found by accident. Sadly, we’re being informed the hard way, but clearer documentation would go a long way to help everyone understand what this function could potentially do.
Yes, having these issues documented would go a long way towards easing frustrations. I’ve long felt that the formula reference support article needs to be improved.
However, here is the bigger issue: We don’t have any way to effectively filter a string for numbers (or any other characters, for that matter).
It would be great if Airtable actually had a broader “Filter” function altogether, which would enable us to filter a string for ANY characters that we specify.
Filtering has been a long-time function in Apple’s FileMaker. It is extremely powerful & frequently used. Here’s the documentation on it:
EDIT 2: After another test, it looks like the REGEX_REPLACE() function alone will do the trick:
IF(String, REGEX_REPLACE(String, "\\D", ""))
That replaces any non-digit character with nothing. Use VALUE() around the result if an actual number is desired. Come to think of it, that same formula would also work in the first case, though care should be taken to look closely at all possible source data to see if any edge cases need to be accounted for.
Thanks for posting that REGEX_EXTRACT function, @Justin_Barrett! :grinning: That will be extremely valuable for future usage. :grinning_face_with_big_eyes: :raised_hands:
I assume that the REGEX_EXTRACT function could also work for filtering/extracting alphabetical characters as well.
Question: I know that the REGEX_EXTRACT function in Airtable FAILS when trying to extract multiple email addresses from a string of text (it will only return the first email address that it finds), so why does it succeed when trying to extract multiple duplicate numbers, such as extracting the multiple 2’s in the year 2020?
It’s all about consecutive characters. In the first example, all of the numbers are together, so they’re all returned together. That’s why I had trouble writing a single Regex to extract all of the numbers in the second example: because they weren’t all packed together. The Regex interpreter sees them in groups. Here’s how they look in the tester at regex101.com :
In both examples, I’m specifically telling it to find a sequence of numbers (the \d symbol represents a single digit, which is functionally equivalent to [0-9], which also finds any character between 0 and 9). The problem is that I haven’t found a way to tell it to take all of the found groups and mash them together into a single result. In my first test of the second example, it only returned the 6, just as it would only return the first email using an email-based Regex. That’s why I changed my approach: remove any sequence of non-digits first, and then remove specific characters after that using SUBSTITUTE().
Edit: I just realized that I forgot to include a couple details, but I was in a rush because my niece is sleeping in the office tonight. :slightly_smiling_face: I’ll try to update it tomorrow when I don’t have to type on my phone.
Wow. That is pretty crafty craftsmanship there, @Justin_Barrett. That’s really impressive. Thank you for coming up with some elegant solutions to a variety of different Airtable limitations.
Here’s what I forgot to mention last night: the + in the Regex examples that I used. That token means to find one or more of whatever precedes it, and that’s how consecutive characters are matched. \d+ means to match one or more consecutive digits, which is why it works for the first example, but not so well for the second because the digits aren’t all together.
I ran a quick test a few minutes ago and found that my original solution to the second problem could be greatly simplified. REGEX_REPLACE() alone is sufficient to remove all of the non-numeric characters, no matter where they are in the source string, so I added an update above. \D represents non-digit characters (the extra backslash is required in the string to escape the backslash required by the non-digit token). By omitting the trailing +, it effectively means “match any non-digit character anywhere in the string”. In short, REGEX_REPLACE() is like SUBSTITUTE() on steroids. :slightly_smiling_face:
I emailed Airtable Support about this, and they updated the formula reference page by linking to this Numeral.js page, although I’m afraid that they have confused matters more now.
I’m not sure specifically what part of that Numeral.js page we’re supposed to be looking at, and I’m not even sure how to interpret the contents of that page.
But at least they’ve acknowledged the issue at this point. :slightly_smiling_face:
EDIT: Oh, they also gave me a REGEX function to extract numbers from a string of text, but it’s not as elegant as @Justin_Barrett’s solution above. Support gave me this formula:
I believe they didn’t understand the issue or they didn’t read the details of the thread. The link to numerals.js is interesting but seems wholly irrelevant. Yeah, dude - javascript handles numbers. Okay - good to know Airtable is using a well-baked library. It’s possible Numerals is doing the transformations, so maybe that explains the link.
And for what it’s worth, I don’t think it’s an issue at all; it’s simply a nuance of transformation however poorly documented it may be. Behaviours like this and other oddities exist vastly across javascript engines - we just don’t get the chance to see them often and when we do, we’re surprised.
Sidebar - one of the best technical writers [ever], Laurie Love, wrote much of FileMaker’s documentation (Miller Technical provided most of the FileMaker docs back in the previous decade). She’s married to Ray Love, chief evangelist and former product manager of the famed dBASE IV.
