Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Interesting discovery: The VALUE() function perfor...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

1
5998
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 12, 2021 07:11 PM

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 the formula `VALUE("**6hxyzb0%%00987**") `

will return 60009.87. The very presence of a percentage symbol in the text string makes `VALUE()`

multiply the extracted numbers by 1%.

Similarly, if the `VALUE()`

function sees a dash in the text string, `VALUE()`

interprets the dash as a minus sign and it multiplies the extracted numbers by -1.

So the formula `VALUE("123-XYZABC")`

will return -123.

Interestingly enough, if it sees 2 dashes, it multiples the 2 negative ones together to get a positive 1.

So the formula `VALUE("123-XYZ-ABC")`

will return a positive 123.

So it is actually counting up all the dashes in the text string and multiplying each one by negative 1.

There might be other strange mathematical functions happening with the `VALUE()`

function as well, but those are the 2 math functions that I discovered today.

These sorts of mathematical functions should probably be clarified in the support article.

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 19, 2021 08:39 AM

Wow, this problem gets **SIGNIFICANTLY WORSE THAN THIS.**

Check out what @CT3 found in this thread:

Long story short: The `VALUE()`

function is completely dysfunctional and broken in Airtable, and should not be used because it cannot be relied upon.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 19, 2021 09:33 AM

Yikes! When I was trying to troubleshoot this, I also discovered the scenario you mentioned that happens upon inclusion of a dash, ie, VALUE(“1-5”) returns -15. I figured you could loosely interpret that as expected behavior—it was recognizing the dash and kind of “leaving it in” to create a negative number.

However your discovery here that it’s actually *multiplying* by *all* these “hidden” values is definitely unexpected and nothing I’ve come across before… Absolutely leads you to wonder what other surprises might be in store using VALUE().

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 19, 2021 01:18 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 20, 2021 05:05 PM

Just so that the solution comes full circle, here’s how to address the extraction issue mentioned above using regular expressions:

```
VALUE(REGEX_REPLACE("**6hxyzb0%%00987**", "\\D", ""))
```

That will remove any non-digit values in the string, leaving only the numbers, and then convert the result into an actual number.