Remove everything before last _ in given field name

[IJH34]

I have a number of fields that were joined together, and thus renamed with various "left_" "right_" before the field name.

e.g.

_left_left_right_field 1

_left_right_field 2

_left_right_right_field 3

what I would like to do, possibly with the combination of dynamic rename and regex, is remove anything prior to and including the last _ in any given field header. 

e.g.

field 1

field 2

field 3

[LordNeilLord]

Hey @IJH34 

Not sure its the most efficient method but I like to use reversestring...

ReverseString(Left(ReverseString([Field1]), FindString(ReverseString(Field1), "_")))

Neil

[Thableaus · Accepted solution]

Hi @IJH34 

Here's a good regex:

REGEX_REPLACE([FieldName], ".*_(.*)", "$1")

Cheers,

[danilang]

And another one

REGEX_Replace([Field1], "(.*_)+", "")

Dan 

[IJH34]

@Thableaus Perfect! Regex is a tricky one for me. 

[MarqueeCrew]

@IJH34,

Back in the day before I knew what RegEx was, I would solve this type of problem with string functions.  While not as fancy as pattern matching, it runs faster.  In fact, with 30 million records it takes 48.4 seconds to calculate your results using RegEx (quite fast!) and only 37.5 seconds to use the following formula:

Right( 
     [Field1], MIN(
FindString(ReverseString([Field1]), "_thgir"),
FindString(ReverseString([Field1]), "_tfel")
     )
)

Bottom line is that you should decide which solution is more easily explained to your team and use RegEx when you're comfortable with it.  I do encourage you to learn it.  @danilang and  @Thableaus gave great RegEx advice  and @LordNeilLord  sent you down the same path that I would have taken you on.  All of the suggestions in the post are solution worthy.

Cheers,

Mark

[LordNeilLord]

I totally agree @MarqueeCrew 

If you don't understand it....don't use it (or learn it quickly)