I’ve long had grumpy thoughts about how Airtable’s
VALUE() function deals with conversion of malformed numerals. For instance,
VALUE('96t7 4B.32') returns
9674.32; personally, I’d be happier if it returned
A couple of months ago, I fat-fingered a value and received a completely unexpected reply. Hoping it might indicate undocumented support for non-base₁₀ values, I began a long trudge through
VALUE(), looking for breadcrumbs. Alas, the function doesn’t magically support hex values or anything else comparably valuable — but it does support some unexpected and potentially useful quirks.
VALUE() does watch for a handful of non-numeric characters when working its conversion magic. Most translate correctly only as the last character of a string; otherwise, they are ignored or, in a few cases, trigger a
NaN (‘not-a-number’) error.
The one character that has significance within a string is the colon (
':'), which Airtable reads as indicating hours, seconds, or minutes, depending upon its placement, and returns the resulting value in seconds. (Presumably, this is an offshoot of Airtable’s ‘duration’ support.) The other interpreted characters are ones commonly used to indicate certain powers of ten.
Here’s a table indicating, on the left, the string values input into
VALUE() and, on the right, the corresponding number values. (These were tested a few months ago, in the midst of my dental adventures, and only recently came to light — and memory. Conceivably, Airtable’s processing <cough!>release notes<cough!> may have changed since then.)
1: 60 1b 1000000000 1k 1000 1m 1000000 1t 1000000000000 11m 11000000 11b 11000000000 111t 111000000000000 1:1 61 1:1000 1060 1:1m NaN 1m: NaN 1:1: 3660 1:1:1: 0 3: 180 100: 6000 1:: 3600 1::.11 3600.11
What can you do with this information? Heck if I know… However, I do know one thing: If there is a possible way to capitalize on it, somehow, somebody out there will find it.