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

HienFerber

New Member
Joined
Feb 24, 2018
Messages
6
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
"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.
 
Upvote 0
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?
 
Upvote 0
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 :oops::oops: :oops::oops: Thank you so much for your reply!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top