Remove duplicate words from a field

[Brad1]

Need some help.

So I have a field that look like this:

Lang_Spoken

ENG, SPA, OTH, CHI, JPN, OTH, OTH

We are keeping track of more languages than the Client is asking for - so every time a language is return that isn't on their list it goes down as "OTH". 

How can I best clean this field of duplicate "OTH" or any other dup for that matter.

Thanks for any help.

[BenMoss · Accepted solution]

First, record ID each row. You can use the 'text to columns' tool, set your delimiter as , and choose the mode 'split to rows'.

You can then unique on the 'Record ID' field and the 'Lang_Spoken' field.

Finally, to bring them back onto a single line you can use the summerize tool, grouping by your ID field and concatting your 'Lang_Spoken' field. Also group by any other fields you may have in your data.

Give this a go, and let us know if it works!

Ben

[Claje · Accepted solution]

Hi,

The easiest way (I know of) to do this is to Use Text to Columns with the setting (output as rows).  Then, you can use the Unique tool to limit to one of each unique value, and the Summarize tool to concatenate the string back together.

If you run into issues with this i can put together a quick example!

[Brad1 · Accepted solution]

Never mind.

[NJT]

Worked great for me with a similar issue!

[OmaratRJL]

Is there a more "compact way to do this? What if there are 50 potential fields. Is there a way that we can use regex to walk through the string and say example:

SYNWH01,SYNWH02,SYNWH4,SYNWH5,SYNWH6,SYNWH7,SYNWH10,SYNWH14,SYNWH15,SYNWH01,SYNWH02,SYNWH4,SYNWH5,SYNWH6,SYNWH7,SYNWH10,SYNWH14,SYNWH15

then produces something like this:

SYNWH01,SYNWH02,SYNWH4,SYNWH5,SYNWH6,SYNWH7,SYNWH10,SYNWH14,SYNWH15,SYNWH01,SYNWH02,SYNWH4,SYNWH5,SYNWH6,SYNWH7,SYNWH10,SYNWH14,SYNWH15

My fix to this was to put in a unique to get rid of the duplicates but my regex limited mind wants to know if there is a regex fix to this?