Rigorous Email Validation in Excel 2013

Question

I am using Excel 2013. I am building a form that the end user will fill in.

I have a cell for users to enter their email address, and I want to build a thorough email validation. I searched a lot online and all I've found is the suggestion to search for the presence of @ and . but if for example the end user types this: @@.com, no error will be flagged.

What is the correct validation?

Note: i tried to use the UDF and the VBscript I found online and neither works for me.

Solution

Adapting Bill Manville's technique, this should work for you. Go into Data Validation and select Custom. Enter this formula (assuming your email address is in cell A2):

=AND(ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1,IFERROR(SEARCH("@",A2)<SEARCH(".",A2,SEARCH("@",A2)),0),NOT(IFERROR(SEARCH("@",A2),0)=1),NOT(IFERROR(SEARCH(".",A2,SEARCH("@",A2))-SEARCH("@",A2),0)=1),LEFT(A2,1)<>".",RIGHT(A2,1)<>".")

Here's the same formula spaced out so it's readable:

=AND(
    ISERROR(FIND(" ",A2))
   ,LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1
   ,IFERROR(SEARCH("@",A2)<SEARCH(".",A2,SEARCH("@",A2)),0)
   ,NOT(IFERROR(SEARCH("@",A2),0)=1)
   ,NOT(IFERROR(SEARCH(".",A2,SEARCH("@",A2))-SEARCH("@",A2),0)=1)
   ,LEFT(A2,1)<>"."
   ,RIGHT(A2,1)<>"."
   )

This checks that:

1. There are no spaces
2. Only one @ symbol
3. There are some characters before the @ symbol
4. At least one . symbol after the @ symbol
5. At least one character between the @ and following . symbols
6. The address doesn't start or end with .

Comments

[Máté Juhász]

"and all i'v found is just searching on (@,.)", "i tried to use the UDF and the VBscript " - those are extremely broad descriptions, please describe what exactly you've tried / found and why that doesn't work for you.

[ganesh]

@OP: Are you loking for a pure excel function solution or is VGA allowed?

[Mohammed Abusaif]

Really thanks for you answer, but where i should put it, i tried to type it the custom validation but it is too long.

[Andi Mohr]

The data validation box seems to only allow 255 characters so I've trimmed the formula down a bit. Try it now.

[ganesh]

I think two @'s are also perfectly legal. To cite the RFC: ` Strings of characters that include characters other than those allowed in atoms may be represented in a quoted string format, where the characters are surrounded by quote'. So "J@ne"@demo.net is also legal.

[Andi Mohr]

You're technically right I guess (normally the best kind), but for any real world application two @s in an email address is way more likely to be an error than not. Can't say I've ever encountered one in real life. If it's of concern then you can simply remove this bit from the formula: ,LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1.

[Andi Mohr]

This is very interesting but it doesn't seem to answer the question - how do you validate email address in Excel? (Sidenote: I know Gmail allow multiple dots in the username - I guess this is another non-compliant implementation)

[ganesh]

Actually, yes you are right. I got completely distracted from the excel part. Let me play around a bit with excel and see if I can come up with something.

[ganesh]

I agree, two @'s should be very rare and likely an error. Howeveer I know multiple people who use things like [email protected]. If I get mail with that in the header I know how to move it to a folder. If I get spam to that address then I know where to complain. Sadly there are many web based validators which barf on an +, apparently some use it as an internal seperator and are poorly written. I can work around it by using another mail address. I cannot work around trusting poorly written interfaces though.

[ganesh]

Ugh. This is harder than it sounds using just excel to find the last @ meant googling around. Without VBA (and InStrRev) it is hard just to find the last @. Closes I am right now is a TRIM(RIGHT(SUBSTITUTE(text," ",REPT(" ",LEN(text))),LEN(text)) combined with a FIND.