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 -