Formular to Extract Numbers out of a string

[Russell_Findlay]

I am looking for a way to extract the first value from a field that has a text result like this

£38,331 - £41,216 per year

It is to simplify the output of a data extraction of salaries

Your help greatly appreciated

[augmented]

Hi Russell. You could try REGEX_EXTRACT(). Like…

IF({field name}, REGEX_EXTRACT({field name}, '^£[0-9,]*'))

Give it a try. Fingers crossed!

[Russell_Findlay]

thanks - didn’t need the if function but the rest worked a treat

[augmented]

I always like to check the field to make sure something is in there so I don’t get ERROR. That’s just me.

[Bill_French]

Russell_Findlay:

didn’t need the if function but the rest worked a treat

You actually do need to conditionally process this transformation because if it ever does fail - and it most certainly will - formula errors take far longer to process than those that fail with grace. One may think such failures are not a big deal, but it is a big deal because formula computations impact the performance of users. Not many realize that your instance of Airtable is finite - everything including but not limited to scripts, formulas, API activity, dashboards, apps, automations - all of it queues up to be processed by – and only by – your instance. This can leave users who simply want to browse data, waiting impatiently behind a long queue of tasks.

[kuovonne]

If there is no number, the REGEX extract will fail. You may also want to wrap the result in VALUE() if you want a numeric value and not just a string that looks like a number.

[Russell_Findlay]

Thanks - I think the original if function was missing an argument so it wouldn’t work. - so I will retry with ,””) and see if it works.

[Justin_Barrett]

Russell_Findlay:

I think the original if function was missing an argument so it wouldn’t work. - so I will retry with ,””) and see if it works.

Yes, it was missing an argument, but it didn’t actually need it. :winking_face: The documentation doesn’t mention this, but the third argument in the IF() function is optional. If you omit it, Airtable returns nothing if the condition is false, leaving the field empty.

If you add an empty string as the third argument, this can sometimes mess up the output of the previous argument. Why? The IF() function is designed to only return a single type of data. If you end it with a string—empty or not—it force-converts the output of the second argument into a string as well.

Here’s an example to demonstrate that. Add a date field named “Date”, then add a formula field that uses this formula:

IF(Date, Date, "")

In theory this should echo the date from the {Date} field if it’s there. However, what you’ll get is the ISO string version of the date instead because that’s the default output when force-converting a datetime into a string. If you leave off the third argument, though, the date will appear as normal.