We help IT Professionals succeed at work.

how to check if a cell is a valid email?

dimensionav
dimensionav asked
on
HI

I would like to check the content of a cell and validate it as an email expression

Thanks
Comment
Watch Question

Rob HensonFinance Analyst
BRONZE EXPERT

Commented:
Depends what you mean as a valid e-mail. In strict terms *@* would be valid because it has the context of

name @ domain

Please be more specific.

Thanks
Rob H
BRONZE EXPERT
Commented:
as Rob says you need to decide how far you want to go but in general:-
Select a cell
Choose Data > Validation
'Allow' a  “Custom” option
=SEARCH(".",A1,(SEARCH("@",A1,1))+2)

This checks that the address has an @ sign and at least one "." which is pretty much the minimum requirement. Beyond that you would have to check it conforms to a regular expression which would check for alpha numerics in appropriate places, take out commas etc. then check the domain is a valid one (.com, .org,  - plus all the country/interest group specific ones) and to be really througough attempt an SMTP connect to verify its a working address - though this last part could get oyu labelled as a spammer

Reg

Author

Commented:
We have a column that corresponds to email but some people has typed two or more email addresses in the same cell and better cases the address has / characters and commas so the idea is an expression that could check the following:

1 check for @ and at least one "." (As you said)
2 check that the email doesnt have "," ";" and "/" characters in it

Thanks
BRONZE EXPERT
Commented:
Its a bit clunky but this will check for ;,, and / and that there is at least one valid (@ + .) address in the cell -  

=IF(ISERR(SEARCH("/",A1)),IF(ISERR(SEARCH(",",A1)),IF(ISERR(SEARCH(";",A1)),IF(NOT(ISERR(SEARCH(".",A1,SEARCH("@",A1)))),"ok","no @ ."),"semi"),"comma"),"slash")

Reg

SANTABABYSoftware Professional
BRONZE EXPERT

Commented:
If you really want to take it further, you can call out to a web service and get a ruling on whether the cell content is a valid e-mail address.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.