formatting data in a column

[raffayrana]

Hi,

I am trying to clean the data in the validation rule column in the attached picture.

Ideally i would like to remove the non-underlined data in the validation rule but the issue is length of undesired characters is variable in each row.

I have added both columns as reference.

Many thanks in advance.

[peterr_h · Accepted solution]

Hi @raffayrana,

If I understand correctly, you are trying to achieve the value in the 'Formula' field from the 'Validation Rule' field? If this is the case, then please see attached - I have two methods for this.

As you can see, the 'Validation Rule' section has the ']' symbol appearing between the items that you wish to keep and those that you don't - I have used this as an identifying feature in both methods. 

Method 1: Uses Regex_Replace to keep only items kept after the ']' symbol. I added this as a new field, but you could also just replace the existing value with this parsed one if you wish.

Method 2: Uses the Text-To-Columns tool to split the field into two new fields, one containing pre- and one containing post-delimiter.

If I've misunderstood, please let me know what I've misunderstood and I'll rectify!

Cheers

[raffayrana]

Hi @peterr_h,

Thank you so for providing multiple solutions. I have not gone so far into my training to understand Regex_Replace (thank you for explaining this to me) but the method 1 has worked an absolute wonder.

I have attached the end result as well.

You are a star!

Kind regards