Number operations in text

[Matt_Sayward]

Airtable doesn’t seem especially forgiving of formulas doing numerical operations when the original cell contained text.

For example, I have a cell describing engine sizes of cars.

They’re pasted in like the following: “1498cc”.

I’d like to show the engine size in litres, which would translate to “1.5 Litres”

What I’d like to do is have a formula substitute out the ‘cc’, then divide the remaining 1498 by 1000 (which would be 1.498), then round the 1.498 to 1 decimal place (1.5), and then put " Litres" at the end of it.

The following formula works exactly as you’d expect in Google Sheets / Excel:

=concatenate(round(substitute({Engine Size}, “cc”, “”)/1000, 1), " Litres")

However, Airtable formulas doesn’t seem to be able to treat ‘1498’ as a number after the ‘cc’ has been removed.

[Pete_Huang · Accepted solution]

You can use VALUE() to convert text to a number once you’ve taken out “cc”. Hope this helps!