Value Formula Returning Numbers not Found in Cell

[Andrew_Miller1]

I am using the Value formula to extract the year from a text field column. In most cases this is working just fine, however, a handful of entries are returning a random string of numbers that do not appear related to the text field that the formula references. Screen capture for reference attached. What could be causing this to happen?

[CT3]

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 up to exabytes!

Not sure if it’s supposed to that, but that’s indeed what’s happening (and why you’re getting such wacky numbers).

Given all you want to pick up is a year and you have no other numbers in the Name column, subbing in this regex extract formula for VALUE() seems to get what you’re actually after (shown above in the year column):

REGEX_EXTRACT(Name,"\\d{1,4}")

Hopefully this helps!

[ScottWorld]

CT3:

The VALUE() formula seems to be responding to memory units of measurement. It’s actually reading 2020 MAINT TB Z as “2020 times 1TB”, IE 2020 x 1099511627776 (the amount of bytes in one Terabyte) = 2221013488107520. Same thing for INSP 2021 A MB West, which it’s reading as “2021 times 1MB”

This is an incredible find on your part! Thank you for posting this!

This heightens the findings that I found in this thread:

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…

Long story short: The VALUE() function in Airtable is completely broken.

[Justin_Barrett]

I posted a solution for this in another thread, but for some bizarre reason (maybe because I’ve got “we’re moving in a week” on the brain?) I didn’t think to post it here.

First off, VALUE() is not broken. That connection to computer storage sizes (KB, MB, etc.) is clearly intentional. Why was it done? No idea. Why isn’t it documented? Also no idea. It should be documented so that people are aware of it.

Documentation frustrations aside, what you want can still be achieved using one of the new Regex functions added recently. In the {Year Extraction} field, use this formula:

IF(Name, REGEX_EXTRACT(Name, "\\d+"))

This will extract any consecutive sequence of digits, leaving the result as a string. If you want to turn that into an actual number, use this:

IF(Name, VALUE(REGEX_EXTRACT(Name, "\\d+")))