Airtable's VALUE() function is broken

[ScottWorld]

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:

1. 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:

Value Formula Returning Numbers not Found in Cell Formulas

Hi Andrew— I can’t find any documented place that lays out this behavior or why it’s happening, but I did figure out what’s going on. The VALUE() formula seems to be responding to memory units of measurement. It’s actually reading 2020 MAINT TBZ as “2020 times 1TB”, IE 2020 x 1099511627776 (the amount of bytes in one Terabyte) = 2221013488107520. Same thing for INSP 2021 AMB West, which it’s reading as “2021 times 1MB” I discovered it’s reading memory units this way, from kilobytes all the way…

2. The VALUE() function interprets certain symbols as operators to perform math on all the extracted numbers from a string. This issue is documented here:

Interesting discovery: The VALUE() function performs math Formulas

This is an interesting little discovery regarding the VALUE() function. On the formula field reference page, it makes it seem like VALUE() will simply extract all the numbers from a text string. But this isn’t true under all circumstances. The VALUE() function will sometimes perform mathematical functions as well, if certain symbols are included within the text string! For example, the formula VALUE("**6hxyzb0**00987**") will extract all the numbers and return 6000987. This is expected. But…

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.

[Justin_Barrett]

ScottWorld:

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.

My gut says that they’re using Numeral.js behind the scenes instead of writing their own string-to-number converter. Looking at the page, I think the “Unformat” section is the part to note. Unfortunately there isn’t a hyperlink just for that section, so you have to scroll to it.

[ScottWorld]

Ah, good sleuthing there, Justin!! :male_detective: :mag_right:

Yes, it seems like the “Unformat” section is where they are pulling from!

And yeah, it was disappointing for me to learn that they’re just borrowing from external libraries to create their own internal functions.

[Bill_French]

ScottWorld:

it was disappointing for me to learn that they’re just borrowing from external libraries to create their own internal functions.

To the contrary, this should not disappoint anyone; it is cause for celebration. In fact, the entire platform should embrace the inclusion of other libraries based on open web standards.

Imagine a world where user/community-defied functions and formulas are simply based on arbitrary javascript blocks. This would be a huge advancement in platform agility and not unlike that which Airtable is already doing inside the platform.