formatting data in a column

Question

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.

Alteryx help.PNG

peterr_h · Accepted Answer

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