Value Formula Returning Numbers not Found in Cell

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?

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!

Screen Shot 2021-02-19 at 10.57.35 AM

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!

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

This heightens the findings that I found in this thread:

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

1 Like

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+")))
1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.