Help

Re: Help needed - Formulae to extract text from text string

Solved
Jump to Solution
1135 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jon_Stephenson
7 - App Architect
7 - App Architect

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.

2 Solutions

Accepted Solutions
Jon_Stephenson
7 - App Architect
7 - App Architect

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.

See Solution in Thread

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

Screenshot 2023-01-09 at 1.37.36 PM.png

See Solution in Thread

5 Replies 5
AirBenderMarcus
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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.

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

Screenshot 2023-01-09 at 1.37.36 PM.png

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

Thank you so much!