How to automatically remove all unacceptable entries from a CSV file?

[geercom]

I have an enormous contacts file in .CSV format with about 15,200 entries. Many entries are nonstandard, repeating the email address in the first name field or something like this. I want to automatically remove all of these nonstandard entries and leave all the ones that have a First and last name and an email address in their appropriate fields. How do you do this?

To make sure you understand the question, here's an illustration:

First Name Last Name Email Address

Bob Smith Bob@Smith.com {this is an example of a good, acceptable, "standard" (as I am using the word) entry}

Bob@Smith.com Bob@Smith.com {this is an example of an unacceptable entry of the kind I would like to remove}

And anything that does not follow the first example would also be nonstandard.

How do I remove all the nonstandard ones all at once?

 

[bukimi]

I'm quite convinced Excel can't recognize it data in cell is a "name" or not. It can only detect all non-standard entries if you define them.
For example, you can make Excel check if @ is added in First name or Last name instead of e-mail address, but then if someone adds # instead of @ it will be marked as OK (unless you define ALL possible mistakes).

 

[oldbrewer]

[TABLE="width: 727"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]first space[/TD]
[TD]second space[/TD]
[TD]"@"[/TD]
[TD]decision[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob Smith Bob@Smith.com[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[TD]KEEP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob@Smith.com Bob@Smith.com[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD]REMOVE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BobSmith Bob@Smith.com[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]13[/TD]
[TD]REMOVE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BobSmithBob@Smith.com[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]12[/TD]
[TD]REMOVE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob@Smith.com[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD]REMOVE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]by finding where the first space is, where the second space is,[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]and where the @ is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]putting 100 if there is no second space[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]and checking if the @ occurs after a second space[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]you get the ones to remove[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]these helper columns can be hidden away to the right[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]