Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Data Type Conversion in Formulas

Text to Numbers
Text to Numbers

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Just because a value looks like an number to human doesn’t mean that Airtable thinks the value is a number.

In Airtable, text is left aligned and numbers are right aligned.

Converting text to a number with VALUE()

Sometimes when doing math, Airtable will convert text that looks like an number into a number for you. However, it is better to explicitly convert the

You can use VALUE() to convert text to a number. But watch out, because VALUE() sometimes looks at the surrounding text when converting a number, which can give unexpected results.

⁠

When possible, I recommend redesigning your base so that you can store the number in a number field instead of a text field.

Converting a Single Select to a Number

Because a single select field has a limited number of choices, it is easy to convert each text based choice to a number.

SWITCH( {Size},

"small", 1,

"medium", 2,

"large", 3

)

Here is the result.

⁠

Note that with this version, if you edit the names of the choices in the single select field, you must also edit the SWITCH() formula.

Embedding the Number in a Single Select Choice

Another technique is to embed the value for the single select choice in the choice name, and then extract the number. With this technique you can change the single select field without having to rewrite the formula field.

IF(

{Size},

VALUE(

REGEX_EXTRACT(

{Size},

"\\d+$"

)

Here is the result

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP