Various oddities in VALUE()

[W_Vann_Hall]

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 #ERROR!.

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.

In brief, 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.

[Justin_Barrett]

I discovered a short while ago that VALUE() also converts computer data sizes to their equivalent byte values:

1 KB               1024
1 MB            1048576
1 GB         1073741824
1 TB      1099511627776

These also work without spaces between the number and the size identifier (i.e. 1MB and 1 MB both work)

[W_Vann_Hall]

Good to know.

Now, is there an abbreviation to differentiate between 1 Gb of RAM (1,073,741,824 bytes) and 1 Gb of drive space (1,000,000,000 bytes)? :winking_face:

[W_Vann_Hall]

BTW, it’s not ideal, but I did find an easy way to determine whether an entered text value is a legitimate numeric value or if it includes fat-fingered non-numeric characters — this provides a workaround for my complaint about VALUE() being too user-friendly. (In the following examples, `{Numeral}’ is a single-line text field that should contain a valid numeric value.)

SUBSTITUTE(
    {Numeral},
    ',',
    ''
    )=VALUE(
        {Numeral}
        )

That will return a 1 if there are no embedded non-numerics — that is, if {Numeral} is a valid numeric value — and a 0 otherwise. (Depending on region, the thousands separator might need to change — but, then, I’m not sure if or under what conditions Airtable supports a thousands separator other than a comma.)

If you’re trying to error-check currency, you’ll need to add a SUBSTITUTE() for the currency indicator, and other formatting quirks — a space character between - or $ and the leftmost digit, for instance, or parentheses for negative values — might need to be taken into account. The following will test for veracity for positive and negative numeric and USD currency values, including those with embedded spaces:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(    
            {Numeral},
            ',',
            ''
            ),
        '$',
        ''
        ),
    ' ',
    ''
    )=VALUE(
        {Numeral}
        )