Trim data after the last '-' based on length

Hello community,

I have the below sample data and I am trying to convert it by using either Trim or Substring function. I am not able to figure out the best way to achieve this.

We have to consider the length of the last part of the string to be either 3 or 4 when performing the operation. There are other values that are more than 4 and those should not be modified.

Input	Expected Output
ABC-12031-AAC	ABC-12031-A
ABC-102003-ABCD	ABC-102003-AB
ABC-102393-BWE	ABC-102393-B

Can someone suggest me the options we have and I can try them?

Thank you

Workflow

Accepted answers

binu_acs

@ssripat3 One way of doing this

SubString_200923.yxmd

All comments

Prometheus

@ssripat3 How many characters are you to trying to keep overall and after the hyphen?

ssripat3

@Prometheus

Everything before the last hyphen stays. If the length of the string after the last hyphen is either 3 or 4. Then we remove 2 characters from the end of string.

Let us say if the String is ABCD or ABC. Then the string should be modified to AB or A.

Prometheus

@ssripat3 How do you know when you want to keep one character after the last hyphen instead of keeping two characters after the last hyphen? If you're always going to keep two characters after the last hyphen, you can use the RegEx tool's Parse function with this expression: ^(.*\-[A-Z]{2}).*

RegEx Parse1.PNG

binu_acs

@ssripat3 One way of doing this

SubString_200923.yxmd

ssripat3

Thank you for the solution @binu_acs . It works like a charm.

ssripat3

@Prometheus

The reason I know is I have another dataset which has these set of strings, but those are already modified according to the logic I mentioned above.

I am trying to compare both of these datasets and do some analysis

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703