How to remove duplicate data (one of 100+ examples in my table: *Yunnan* & * Yunnan*)

[HienFerber]

I have a data with a column full of duplicated values like the example in my title. I know we can use TRIM function to remove the space, but there are records like "South America" with a valid space so I can't apply TRIM for all. Also, there are so many examples like * Yunnan* that it is very tedious to fix the duplications one by one. There must be another way to address this problem? Thank you in advance for your help!

 

[HienFerber]

"Also, there are so many examples like * Yunnan* that it is very tedious to fix the duplications one by one" --> I mean it is very tedious to use my judgement to fix each case of duplication.

 

[Peter_SSs]

Welcome to the MrExcel board!

Why is TRIM an issue with a value like "South America"? After all, if you TRIM()
"South America"
"South America "
" South America"
"South    America"
etc, they all result in "South America" & you could then remove duplicates?

 

[HienFerber]

Welcome to the MrExcel board!

Why is TRIM an issue with a value like "South America"? After all, if you TRIM()
"South America"
"South America "
" South America"
"South America"
etc, they all result in "South America" & you could then remove duplicates?

Oh I always thought TRIM is for removing all the spaces, not before and after. I even remember trying it in the past, but it must have been something else Thank you so much for your reply!

 

[Peter_SSs]

Thank you so much for your reply!

You are welcome.
TRIM removes all leading & trailing spaces and for any 'internal' spaces reduces each to 1 space. So in the last example in post #3 I think there were 4 spaces between the two words and TRIM would reduce that to 1 space.