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

[Steve_Vo]

Hi Justin,

I’ve tried your revised formula but got an error message. What’s missing?

VALUE(TRIM(RIGHT(SUBSTITUTE(LEFT(Expenses Accounts, LEN(Expenses Accounts) - 2), " “, REPT(” ", 10)), 12)))

[Justin_Barrett]

When a field name contains spaces, it must be wrapped in curly braces (like how that rhymes?) :winking_face: Also, double-check your quotes (unless they just got curled by posting here).

VALUE(TRIM(RIGHT(SUBSTITUTE(LEFT({Expenses Accounts}, LEN({Expenses Accounts}) - 2), " ", REPT(" ", 10)), 12)))

FWIW, to force the forum parser to format text as pre-formatted text, indent single lines with four spaces (as above). For blocks of code, type a triplet of inverted apostrophe characters ( ` ) on their own lines both before and after. The text in this screenshot:

…becomes this when formatted…

This text is pre-formatted.
Some text becomes automatically color-coded this way, like "strings".

[Steve_Vo]

Justin,

It works, thanks a million!

[Laura_Flores]

Hello, I would like some help to use trim, please see the image, thanks!

[Justin_Barrett]

Welcome to the community, @Laura_Flores! :grinning_face_with_big_eyes: I’m guessing you’re going for a more general approach—some records might have a different word (or series of words) in place of “FEASIBILITY”, different numbers, etc.—so I built my solution to not look for too many specific features in the text. The only things I’m counting on being consistent are the separators (" - "), and each task name/label ending in a question mark.

To solve this, I actually found it easier to work backwards. As you found, pulling {TASK'S WEIGHT} off the end with RIGHT() is easy. From there, I used that extraction to aid in pulling off the number before it for {GATE'S WEIGHT}, and the combination of those two to extract the text for {GATE} (“FEASIBILITY”, or whatever text may be after the question but before the first number). And I didn’t need to use TRIM(). :slightly_smiling_face: Here are my test results:

And here are the formulas. First for {GATE}:

MID(SUBSTITUTE({PROJECT TASKS}, " - " & {GATE'S WEIGHT} & " - " & {TASK'S WEIGHT}, ""), FIND("? - ", {PROJECT TASKS}) + 4, 50)

Next for {GATE'S WEIGHT}:

IF({PROJECT TASKS}, VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE({PROJECT TASKS}, " - ", "|"), "|" & {TASK'S WEIGHT}, ""), 2)))

And finally {TASK'S WEIGHT}:

IF({PROJECT TASKS}, VALUE(RIGHT({PROJECT TASKS}, 2)))

Notice that I wrapped the numerical extractions in VALUE() to turn them into numbers, in case you want to use those in any other formula calculations.

[Laura_Flores]

Thanks so much Justin!! I appreciate your help!

[Arthur_Seabra]

I agree 100% and I only saw this one solution, where he like woke up and had it. That’s like Jedi Mind trick stuff.

[Nicolas_Lapierr]

Clever! This just saved me a lot of headache :thumbs_up: I can’t count how many of your posts have helped me, thank you so much for your time here!

[Louise_Nighting]

@Justin_Barrett I’m hoping your exceptional skills in this department can help me as well. I am trying to create a formula to automatically pull the value out of a series of currencies and I keep getting an ERROR message, so I’m clearly doing something wrong. The problem is there are several different currencies and value lengths.

Is there a formula I can use to only show the numerical value (including commas) for entries similar to these? I only want the numbers (example: 15,000 and 200,000 - no text or currency symbol)
USD $15,000
USD $200,000
AUD $1,000,000
USD $13,000
CAD $10,000
JPY ¥20,000,000
USD $5,000

Thank you very much in advance!!

[Justin_Barrett]

@Louise_Nightingale Regex to the re(gex)scue! :winking_face:

IF(Currency, REGEX_EXTRACT(Currency, "[\\d,]+"))

Breakdown…

• The \d token matches any digit (the extra backslash is required to escape the backslash needed by the token), and the comma matches a literal comma
• The brackets tell the regex interpreter to match any of the contained characters/tokens
• The + at the end tells the interpreter to make that previous match one or more times