Return only unique values, deliminated

[jimbomcmucka]

Hi all,

Firstly, thanks so much for those that have helped with a few questions I have had over the last few days! I feel like I have been on here pestering you guys a LOT!
I am hoping this will be my final question (for now)..

I have a data source that lists multiple countries in a single cell, deliminated with a "|". Eg. "CostaRica|Brazil|Mexico|Chile|Netherlands|UnitedKingdom|Ireland|Russia". Sometimes a country will appear twice in this data.

I am returning this value in a simple VLOOKUP ( =VLOOKUP(A4,'Sheet1'!H:O,8,1) ).

Is there a way that I can return only the unique countries? So for example if the data source was ""CostaRica|Brazil|Mexico|CostaRica", remove the duplicate and return ""CostaRica|Brazil|Mexico".

Thanks in advance!
Jim

 

[bobsan42]

If the data is joined like this in the source, you can remove duplicates like this:

=TEXTJOIN("|",TRUE,UNIQUE(TEXTSPLIT(A1,",","|")))

 

[jimbomcmucka]

If the data is joined like this in the source, you can remove duplicates like this:

=TEXTJOIN("|",TRUE,UNIQUE(TEXTSPLIT(A1,",","|")))

Thanks so much! Works like a dream.