• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicate email addresses in same cell

N

NURDS

New Member
Hi I have a long list of exported email addresses. Many of them have duplicate email addresses in the same cell and I need to remove all duplicates. The easiest way I would approach this is to identify the occurrences of "@" in a given cell and identify all those with value >1, but can't figure out how to do this? Help would be appreciated. Duplicate cells look like this:

name@company.com name2@company.com

Thanks
Steve
 
Hi. Thanks.
I have searched the forums and can't find an answer - any help on formula structure would be grateful.
 
If your data is laid out as per sample with a space between the addresses just use text to column and then delete the duplicate column
 
Thanks - just had a quick look and I'm afraid that would not work. Due to years of spurious entries, many have no gaps, some are separated by commas, slashes, semi-colons, full stops! it's a bit of a mess. If I could at least just highlight the cells that are duplicated, I can manually remove the spurious content? The only common denominator I can see is that they should all have more than one "@" in the cell? Thank you.
 
If there's nothing common, no feasible pattern other than visually guessing then it will be hard to get rid of duplicates.

Will it be feasible for you to build the list of domain suffixes that complete email-ID
e.g. .com, .net, .in etc.
 
Hi. Thanks, but the problem is not duplicate rows - these have already been taken care of. All I need to do is identify the lines where more than one email address exists in the cell?
 
Assuming your data starts in cell A2, write following formula and copy down:
=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"@","")))>1,"Check","OK")
and then check all rows showing "Check".
 
You must log in or register to reply here.
Back
Top