Help

Formula for extracting last text/digits from a code

Topic Labels: Formulas
12310 22
cancel
Showing results for 
Search instead for 
Did you mean: 
Christina_K
4 - Data Explorer
4 - Data Explorer

hi i have a column with codes like this “0010o00002Fs719AAB”

What will the formula to extract the 8 of the code?

it should become Fs719AAB.

Thanks in advance!

Chris

22 Replies 22

Thank you for the swift response, @Justin_Barrett - I’m sadly getting that same pesky #ERROR! message.

I didn’t mention that the currency data I’m trying to pull from is a lookup result from another tab. Does that matter?

I’ve attached a screen shot in case it’s useful
Screen Shot 2021-07-15 at 2.43.55 PM

Yes. Lookup fields return arrays, not single values, and Airtable’s regular expression functions only operate on strings. This can be fixed by concatenating the lookup field output with an empty string, which force-converts it to a string.

IF({Reward Total}, REGEX_EXTRACT({Reward Total} & "", "[\\d,]+"))

When a lookup field is only pulling from a single linked record (as in your situation), it still returns an array, but there are limited instances where Airtable will auto-convert that array to a single data type. It’s tough to keep track of all of the options, so I ran a lot of tests and documented my findings here:

I’m also working on a course all about Airtable formulas. It’s not going to be ready for another month or two, but my rough edit currently runs over an hour. If learning the ins and outs of Airtable’s formula system is something that interests you, keep an eye out for when the course is released.

Thanks @Justin_Barrett - late response from me but appreciate your help!

Would love to join your formula course once it’s available!