Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Help needed - Formulae to extract text from text string

Solved
Jump to Solution
163 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

5 Replies 5

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.

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 @Adam_TheTimeSav ! I was just about to post my regex expression too, which technically worked but wasn't as efficient as yours.

Thank you so much!