Remove duplicate words from a field

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.

Output

Parse

Expression

Accepted answers

BenMoss

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

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

Never mind.

All comments

BenMoss

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.

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

Ben

Claje

Hi,

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

Brad1

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?

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335