Excel Remove duplicate values that has some spaces

I'm trying to remove the duplicates from an excel column. I have tried to use the in build remove duplicate values function present in excel.

Initially I had the following data

 University of South Florida College of Medicine University of South Florida College of Medicine University of South Florida College of Medicine University of South Florida College of Medicine University of South Florida Morsani College of Medicine University of South Florida Morsani College of Medicine University of South Florida Morsani College of Medicine University of South Florida Morsani College of Medicine University of Tennessee College of Medicine University of Tennessee College of Medicine University of Tennessee College of Medicine University of Texas Medical Branch University of Texas Medical Branch University of Texas Medical School University of Texas Medical School University of Texas Medical School 

Upon using the remove duplicates function I was still left behind few duplicates After further analysis I realized the second row has some extra space at the end

 University of South Florida College of Medicine University of South Florida College of Medicine University of South Florida Morsani College of Medicine University of South Florida Morsani College of Medicine University of Tennessee College of Medicine University of Tennessee College of Medicine University of Texas Medical Branch University of Texas Medical Branch University of Texas Medical School University of Texas Medical School 

 University of Texas Medical SchoolNOSPACE University of Texas Medical School ONESPACE

How to remove the duplicates that has some extra space at the end ?

1 Answer

Use TRIM first, which will remove any additional trailing (or leading) spaces from your source data.

So if your data starts in A2, write the following in B2:

=TRIM(A2)

and copy or drag that down in column B until you've covered all the data. For extra cleanliness you can now copy everything in B, then paste values, and remove column A. Then use Remove Duplicates on your new clean data.