Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Value Formula Returning Numbers not Found in Cell

Topic Labels: Formulas
2523 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrew_Miller1
4 - Data Explorer
4 - Data Explorer

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?

Screen Shot 2021-02-18 at 4.41.33 PM

3 Replies 3
CT3
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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