Remove Duplicate values contained within the same field

[RCern]

I have a dataset in Alteryx that has 81 fields.  In most of the fields, there are concatenated values separated by a pipe.   How do I clean the data in each field whereby I only leave in distinct values within each field and remove the duplicate values? 

I attached a dummy example for one customer.  Do I need a Macro or Regex expression to remove duplicate values in a field?  The official customer list I have is 60,000 customers so the type of data in the fields will vary.  I just need to know what the most efficient process would be to remove duplicates and the Pipes from within each field.

Any help you can provide would be TREMENDOUSLY appreciated!

Thank you.

[flying008]

Hi, @RCern 

Another dynamic solution just one formula for you: (the transpose tool is only for demo.)

Spoiler

IIF(IsString([_CurrentField_]), REGEX_Replace(REGEX_Replace(Trim([_CurrentField_]), '([^|]+)(\|\1)+', '$1'), '[\|]{2,}', Null()), [_CurrentField_])

[Hsandness]

Without having the exact input data and expected output data it's difficult for me to troubleshoot. If you can provide those with obscured data so customer information is protected I can take a look. Otherwise, I would go with @binu_acs solution, it's more straightforward and should allow for easier review in the future.

[RCern]

@flying008 can you show me how you configured the tool or can you share that worklfow?