Gallery
Kuovonne's Guide to Airtable
Share
Explore
Kuovonne's Guide to Airtable
Data Type Conversion in Formulas

Text to Numbers

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.
ok

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.
image.png
error

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.
image.png
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
image.png
Thank Kuovonne for creating this content!

Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.