Removing inexact duplicates

[lookimadeahar]

Hello! I have large dataset of institutions that contains messy duplicate entries for some of the institutions, like the following:

1. University of Nairobi libraries
2. University of nairobi - department of biology
3. University of nairobi, dept of zoology
4. The university of Nairobi
5. University of Nairobi Faculty of Engineering
Etc.

Is there a way to (1) remove the inexact duplicates so I’m only left with one of these entries per institution, and/or (2) replace such entries in the example with just “University of Nairobi”?

Thanks in advance!

[PaulM100]

You could create a separate list containing possible entries(like University of Nairobi") let's say in C2:C20. Assuming that your data is in A2 and down, then in B2 you could try a formula like the one below and afterwards you can remove them or filter them:
=IF(COUNTIF($A$1:$A2,INDEX($C$2:$C$20,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$20,A2)),0)))>1,"Remove",INDEX($C$2:$C$20,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$20,A2)),0))))
This is an CSE formula, so press Ctrl+Shift+Enter to activate it.