remove duplicates based on partial text match

**smelkin** · 06-15-2011, 05:05 PM

So I have been toying with a way to do this in excel, and my brain is not big enough to figure it out apparently.

I have a massive spreadsheet with web URLs in column A and other columns. Want to remove all but the first occurrence of a root URL from the sheet.

Col A example:

http://www.anydomain.com/userid=12
http://www.anydomain.com/services
http://anydomain.com/services/righton.asp
http://anydomain.com/jartnaff.php

So end result would be to generate a duplicate free spreadsheet that contains unique value rows between the // and the first / which in the above example would be:

http://www.anydomain.com/userid=12
http://anydomain.com/services/righton.asp

any help would be appreciated, I tried several things.

THanks.

**NBVC** · 06-16-2011, 09:05 AM

Try this.

Add a helper column to identify the section between the slashes...

So if your data starts in A1, then in B1 enter formula:

=MID(A1,FIND("//",A1),FIND("^^",SUBSTITUTE(A1,"/","^^",3))-FIND("//",A1)+1)

copied down.

Then select columns A and B and go to Data tab and click Remove Duplicates from the Data tools section of the ribbon.

Uncheck Column A and leave only Column B selected.

Click ok.

You can then delete the formula column.

**smelkin** · 06-16-2011, 09:22 AM

NBVC,

I tried it and it works like a charm. Thanks much! So that I understand the syntax here, I get the find "//" but you lose me after that with the find "^^" and also the ,3 +1 . Can you explain a bit? Grazie

**NBVC** · 06-16-2011, 09:45 AM

This part: FIND("^^",SUBSTITUTE(A1,"/","^^",3))-FIND("//",A1)+1 is the "number of characters" argument of the parent MID function.

So, basically, because there are known to be 3 slashes in each cell, I want to substitute the 3rd one with a random, but likely not used symbol, e.g. ^^.

In this function, then we replace the 3rd / with a ^^ and then Find it's position and then subtract the position where the first double slash // occurs and add 1 to include that last slash.