Extract Text Formula Needed

[jboschee]

Hi all,

Wonder if anyone can help, I have a text string 001_USA_CALIFORNIA_LA_FIRST_ST and I want to be able to extract CALIFORNIA_LA, but also have the ability to apply this to other state/city combinations.

Any help would be appreciated.

Thanks

[MarqueeCrew · Accepted solution]

Regex_replace([field],”.*?_.*?_(.*?_.*?)_.*”,’$1’)

that might work.    

Cheers,

Mark

[jboschee]

Thanks Mark!  That works perfectly.  Can you explain the theory behind it?  I haven't used Regex much.

[MarqueeCrew]

. Is any character
* is 0 or more
.*_ would be every character until the last underscore in the field.
.*?_ is all characters until the first underscore.

( is the start of a group
) is the end of a group

We looked for the group and ‘$1’ is that group.

<3 regex

[jboschee]

Thanks Mark.  Appreciate the explanation.