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.