Formula for extracting last text/digits from a code

[Christina_K]

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

[Justin_Barrett]

RIGHT({Field name}, 8)

More formula reference:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[Christina_K]

Thanks so much! worked like a charm

[Steve_Vo]

Hi,

I have three examples below. What code required to extract just the dollar amount for all three examples?

WEEKLY PAY 9888.95 N

FULL TANK-FUEL -80.00 N

T8IPK68700 300.00 N

Thanks!

[Justin_Barrett]

I can’t think of a way to extract that number because there are too many inconsistencies in the formatting.

1. The characters before the dollar amount include an inconsistent number of spaces, so splitting by spaces isn’t reliable.
2. The leading characters also might contain numbers, so it’s not as easy as wrapping VALUE() around the whole thing.
3. The dollar values can be small or large, positive or negative, so extracting a consistent number of characters from the end of the string (after stripping off the " N") won’t work.

There may be a way to do it by running a bunch of nested tests to check for various combinations of separating spaces, but IMO it would be more of a headache than it’s worth.

[Steve_Vo]

I knew it would be a challenge, thanks for your help!

[Justin_Barrett]

Okay, I don’t know what it is lately, but the past two days I’ve woken up with solutions to certain problems in my head, and today it was this one. I recalled a trick I’d seen elsewhere, and it works quite well in this case. Here’s the formula to extract that value. {Text} is my field where I put the source text; change it to match your own field name:

VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(Text, " N", ""), " ", REPT(" ", 10)), 12)))

At first it might look like the decimals are being ignored, but they’re not. That’s just formatting. Format the field as currency ,or decimal with two places, and you’ll see them.

[Steve_Vo]

Justin,

You are an Airtable Jedi! The force are with you, it works!

[Steve_Vo]

Hi Justin,

How would you re-write the code so that it will works my other text lines that ending with A, S or something else? I have all these in the same field.

[Justin_Barrett]

This should work, as long as the amount to trim off the right end is always two characters (i.e. a space and a single letter).

VALUE(TRIM(RIGHT(SUBSTITUTE(LEFT(Text, LEN(Text) - 2), " ", REPT(" ", 10)), 12)))