Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an Excel
worksheet, is there a good way to identify invalid addresses? (for example,
if the '@' is missing) ?

thanks in advance
Becky

You probably could do better with VBA, but try this.
Copy your addresses to a new worksheet
Replace Replace what:= *@*.???
All the BAD will remain


"Becky" wrote:

Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an Excel
worksheet, is there a good way to identify invalid addresses? (for example,
if the '@' is missing) ?

thanks in advance
Becky

Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE

"Becky" wrote:

Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an Excel
worksheet, is there a good way to identify invalid addresses? (for example,
if the '@' is missing) ?

thanks in advance
Becky

This will not account for longer domain designations such as .info, or
international ones like .ca, .ru, etc.

You want a function that searches for at least one period after @, as
well as the @ symbol. I think you can do both with one stone. This
function returns TRUE if the address is invalid.

=ISERROR(1/(LEN(MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)))-
LEN(SUBSTITUTE(MID(A1,FIND("@",A1,1)+1,LEN(A1)-
FIND("@",A1,1)),".",""))))

1 is divided by the difference of the two LEN() functions. The
difference of the two LEN() functions will give you the number of
periods after the @ symbol. If @ symbol is not present, FIND() will
return an error that will filter through to ISERROR() function. If @
is present but no periods exist after it, the division will return an
error also filtering through to ISERROR().


On Aug 14, 10:50 pm, Tevuna wrote:
Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE



"Becky" wrote:
Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an Excel
worksheet, is there a good way to identify invalid addresses? (for example,
if the '@' is missing) ?

thanks in advance
Becky- Hide quoted text -

- Show quoted text -

Your formula accepts the following as valid email addresses


ssssssssssssss









which only shows that it is basically impossible unless you use a list
of all country abbreviations in use for emails that the formula check
against plus the different domains and that it also checks the number of
characters in the last string after the period.




--
Regards,

Peo Sjoblom





"iliace" wrote in message
ps.com...
This will not account for longer domain designations such as .info, or
international ones like .ca, .ru, etc.

You want a function that searches for at least one period after @, as
well as the @ symbol. I think you can do both with one stone. This
function returns TRUE if the address is invalid.

=ISERROR(1/(LEN(MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)))-
LEN(SUBSTITUTE(MID(A1,FIND("@",A1,1)+1,LEN(A1)-
FIND("@",A1,1)),".",""))))

1 is divided by the difference of the two LEN() functions. The
difference of the two LEN() functions will give you the number of
periods after the @ symbol. If @ symbol is not present, FIND() will
return an error that will filter through to ISERROR() function. If @
is present but no periods exist after it, the division will return an
error also filtering through to ISERROR().


On Aug 14, 10:50 pm, Tevuna wrote:
Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE



"Becky" wrote:
Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an
Excel
worksheet, is there a good way to identify invalid addresses? (for
example,
if the '@' is missing) ?

thanks in advance
Becky- Hide quoted text -

- Show quoted text -

If you want to account for that possibility - which I would say is
soundness and not validity - you can use this (press Ctrl+Shift+Enter
instead of Enter):

=OR(ISERROR(SEARCH("*@*.*",A1)),ISERROR(MATCH(TRUE ,MID(A1,FIND(CHAR(150),SUBSTITUTE(A1,".",CHAR(150) ,LEN(A1)-
LEN(SUBSTITUTE(A1,".",""))))+1,LEN(A1)-
FIND(CHAR(150),SUBSTITUTE(A1,".",CHAR(150),LEN(A1)-
LEN(SUBSTITUTE(A1,".","")))))={"biz","com","edu"," gov","info","int","mil","name","net","org","aero", "asia","cat","coop","jobs","mobi","museum","pro"," tel","travel","arpa","root","berlin","bzh","cym"," gal","geo","kid","kids","lat","mail","nyc","post", "sco","web","xxx","nato","example","invalid","loca lhost","test","bitnet","csnet","ip","local","onion ","uucp","ac","ad","ae","af","ag","ai","al","am"," an","ao","aq","ar","as","at","au","aw","ax","az"," ba","bb","bd","be","bf","bg","bh","bi","bj","bm"," bn","bo","br","bs","bt","bw","by","bz","ca","cc"," cd","cf","cg","ch","ci","ck","cl","cm","cn","co"," cr","cu","cv","cx","cy","cz","de","dj","dk","dm"," do","dz","ec","ee","eg","er","es","et","eu","fi"," fj","fk","fm","fo","fr","ga","gd","ge","gf","gg"," gh","gi","gl","gm","gn","gp","gq","gr","gs","gt"," gu","gw","gy","hk","hm","hn","hr","ht","hu","id"," ie","il","im","in","io","iq","ir","is","it","je"," jm","jo","jp","ke","kg","kh","ki","km","kn","kr"," kw","ky","kz","la","lb","lc","li","lk","lr","ls"," lt","lu","lv","ly","ma","mc","md","mg","mh","mk"," ml","mm","mn","mo","mp","mq","mr","ms","mt","mu"," mv","mw","mx","my","mz","na","nc","ne","nf","ng"," ni","nl","no","np","nr","nu","nz","om","pa","pe"," pf","pg","ph","pk","pl","pn","pr","ps","pt","pw"," py","qa","re","ro","ru","rw","sa","sb","sc","sd"," se","sg","sh","si","sk","sl","sm","sn","sr","st"," sv","sy","sz","tc","td","tf","tg","th","tj","tk"," tl","tm","tn","to","tr","tt","tv","tw","tz","ua"," ug","uk","us","uy","uz","va","vc","ve","vg","vi"," vn","vu","wf","ws","ye","yu","za","zm","zw","eh"," kp","me","rs","um","bv","gb","pm","sj","so","yt"," su","tp","bu","cs","dd","zr"},
0)))

On Aug 15, 1:30 pm, "Peo Sjoblom" wrote:
Your formula accepts the following as valid email addresses









which only shows that it is basically impossible unless you use a list
of all country abbreviations in use for emails that the formula check
against plus the different domains and that it also checks the number of
characters in the last string after the period.

--
Regards,

Peo Sjoblom

"iliace" wrote in message

ps.com...



This will not account for longer domain designations such as .info, or
international ones like .ca, .ru, etc.

You want a function that searches for at least one period after @, as
well as the @ symbol. I think you can do both with one stone. This
function returns TRUE if the address is invalid.

=ISERROR(1/(LEN(MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)))-
LEN(SUBSTITUTE(MID(A1,FIND("@",A1,1)+1,LEN(A1)-
FIND("@",A1,1)),".",""))))

1 is divided by the difference of the two LEN() functions. The
difference of the two LEN() functions will give you the number of
periods after the @ symbol. If @ symbol is not present, FIND() will
return an error that will filter through to ISERROR() function. If @
is present but no periods exist after it, the division will return an
error also filtering through to ISERROR().

On Aug 14, 10:50 pm, Tevuna wrote:
Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE

"Becky" wrote:
Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an
Excel
worksheet, is there a good way to identify invalid addresses? (for
example,
if the '@' is missing) ?

thanks in advance
Becky- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I didn't say I wanted it, besides my guess is that your formula is too long


--
Regards,

Peo Sjoblom



"iliace" wrote in message
ups.com...
If you want to account for that possibility - which I would say is
soundness and not validity - you can use this (press Ctrl+Shift+Enter
instead of Enter):

=OR(ISERROR(SEARCH("*@*.*",A1)),ISERROR(MATCH(TRUE ,MID(A1,FIND(CHAR(150),SUBSTITUTE(A1,".",CHAR(150) ,LEN(A1)-
LEN(SUBSTITUTE(A1,".",""))))+1,LEN(A1)-
FIND(CHAR(150),SUBSTITUTE(A1,".",CHAR(150),LEN(A1)-
LEN(SUBSTITUTE(A1,".","")))))={"biz","com","edu"," gov","info","int","mil","name","net","org","aero", "asia","cat","coop","jobs","mobi","museum","pro"," tel","travel","arpa","root","berlin","bzh","cym"," gal","geo","kid","kids","lat","mail","nyc","post", "sco","web","xxx","nato","example","invalid","loca lhost","test","bitnet","csnet","ip","local","onion ","uucp","ac","ad","ae","af","ag","ai","al","am"," an","ao","aq","ar","as","at","au","aw","ax","az"," ba","bb","bd","be","bf","bg","bh","bi","bj","bm"," bn","bo","br","bs","bt","bw","by","bz","ca","cc"," cd","cf","cg","ch","ci","ck","cl","cm","cn","co"," cr","cu","cv","cx","cy","cz","de","dj","dk","dm"," do","dz","ec","ee","eg","er","es","et","eu","fi"," fj","fk","fm","fo","fr","ga","gd","ge","gf","gg"," gh","gi","gl","gm","gn","gp","gq","gr","gs","gt"," gu","gw","gy","hk","hm","hn","hr","ht","hu","id"," ie","il","im","in","io","iq","ir","is","it","je"," jm","jo","jp","ke","kg","kh","ki","km","kn","kr"," kw","ky","kz","la","lb","lc","li","lk","lr","ls"," lt","lu","lv","ly","ma","mc","md","mg","mh","mk"," ml","mm","mn","mo","mp","mq","mr","ms","mt","mu"," mv","mw","mx","my","mz","na","nc","ne","nf","ng"," ni","nl","no","np","nr","nu","nz","om","pa","pe"," pf","pg","ph","pk","pl","pn","pr","ps","pt","pw"," py","qa","re","ro","ru","rw","sa","sb","sc","sd"," se","sg","sh","si","sk","sl","sm","sn","sr","st"," sv","sy","sz","tc","td","tf","tg","th","tj","tk"," tl","tm","tn","to","tr","tt","tv","tw","tz","ua"," ug","uk","us","uy","uz","va","vc","ve","vg","vi"," vn","vu","wf","ws","ye","yu","za","zm","zw","eh"," kp","me","rs","um","bv","gb","pm","sj","so","yt"," su","tp","bu","cs","dd","zr"},
0)))

On Aug 15, 1:30 pm, "Peo Sjoblom" wrote:
Your formula accepts the following as valid email addresses









which only shows that it is basically impossible unless you use a list
of all country abbreviations in use for emails that the formula check
against plus the different domains and that it also checks the number of
characters in the last string after the period.

--
Regards,

Peo Sjoblom

"iliace" wrote in message

ps.com...



This will not account for longer domain designations such as .info, or
international ones like .ca, .ru, etc.

You want a function that searches for at least one period after @, as
well as the @ symbol. I think you can do both with one stone. This
function returns TRUE if the address is invalid.

=ISERROR(1/(LEN(MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)))-
LEN(SUBSTITUTE(MID(A1,FIND("@",A1,1)+1,LEN(A1)-
FIND("@",A1,1)),".",""))))

1 is divided by the difference of the two LEN() functions. The
difference of the two LEN() functions will give you the number of
periods after the @ symbol. If @ symbol is not present, FIND() will
return an error that will filter through to ISERROR() function. If @
is present but no periods exist after it, the division will return an
error also filtering through to ISERROR().

On Aug 14, 10:50 pm, Tevuna wrote:
Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE

"Becky" wrote:
Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an
Excel
worksheet, is there a good way to identify invalid addresses? (for
example,
if the '@' is missing) ?

thanks in advance
Becky- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Sorry, just realized there's a much easier way to do this.

=ISERROR(SEARCH("*@*.*",A1))

I completely forgot that SEARCH() supports wildcard characters.


On Aug 15, 12:50 pm, iliace wrote:
This will not account for longer domain designations such as .info, or
international ones like .ca, .ru, etc.

You want a function that searches for at least one period after @, as
well as the @ symbol. I think you can do both with one stone. This
function returns TRUE if the address is invalid.

=ISERROR(1/(LEN(MID(A1,FIND("@",A1,1)+1,LEN(A1)-FIND("@",A1,1)))-
LEN(SUBSTITUTE(MID(A1,FIND("@",A1,1)+1,LEN(A1)-
FIND("@",A1,1)),".",""))))

1 is divided by the difference of the two LEN() functions. The
difference of the two LEN() functions will give you the number of
periods after the @ symbol. If @ symbol is not present, FIND() will
return an error that will filter through to ISERROR() function. If @
is present but no periods exist after it, the division will return an
error also filtering through to ISERROR().

On Aug 14, 10:50 pm, Tevuna wrote:



Or rather do this:
Assuming your address are in column A, copy this down a column
=AND(LEFT(RIGHT(A1,4),1)=".",NOT(ISERR(FIND("@",A1 ))))
Invalid address are singled out as FALSE

"Becky" wrote:
Hi. I'm not sure I'm in the right forum, but here goes.

Given a list of several hundred e-mail addresses in a column of an Excel
worksheet, is there a good way to identify invalid addresses? (for example,
if the '@' is missing) ?

thanks in advance
Becky- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -