Help needed - Formulae to extract text from text string

[Jon_Stephenson]

If I have these values in a formula field, 

0_99_123_456_abc_wantedtext1

_ppp_876_333_wantedtext2

hhh_kol_ggb_hhh_sss_wantedtext3

can someone please tell me how to extract the following text into a new field please?

wantedtext1

wantedtext2

wantedtext3

Thanks in advance.

[AirBenderMarcus]

There are a couple of approaches to this, but if the "wantedtext" bits are all the same number of characters you can use the RIGHT() function, which extracts text starting from the end of the string up to the number of characters you specify.

Otherwise, it's a matter of figuring out if there are other consistent patterns in your naming convention we could consistently match. For instance, since we know the underscore always precedes the "wantedtext", we could right a match rule for that. It all depends on how uniform your conventions are and how they are generated.

[Jon_Stephenson · Accepted solution]

Thanks for the reply.  Right$ wont work as variable length, but what will stay the same is that it will be the string after the last underscored every time.

[TheTimeSavingCo · Accepted solution]

Hi Jon, try out: `REGEX_EXTRACT(Name, "[^_]*$")`

 

[AirBenderMarcus]

That's brilliant @TheTimeSavingCo ! I was just about to post my regex expression too, which technically worked but wasn't as efficient as yours.

[Jon_Stephenson]

Thank you so much!