0

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.

  • 1
    "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. – Máté Juhász Jul 13 '16 at 9:56
  • @OP: Are you loking for a pure excel function solution or is VGA allowed? – Hennes Jul 13 '16 at 21:44
1

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 .
| improve this answer | |
  • Really thanks for you answer, but where i should put it, i tried to type it the custom validation but it is too long. – Mohammed Abusaif Jul 13 '16 at 11:58
  • The data validation box seems to only allow 255 characters so I've trimmed the formula down a bit. Try it now. – Andi Mohr Jul 13 '16 at 12:16
  • 1
    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. – Hennes Jul 13 '16 at 12:49
  • 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 Jul 13 '16 at 14:46
  • I agree, two @'s should be very rare and likely an error. Howeveer I know multiple people who use things like ny.name+ibood@mydomain.tld. 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. – Hennes Jul 13 '16 at 21:41
0

Email address format is well defined in RFC 2822.

Basically you can simplify it as follows.

You have two parts: USER @ Domain.

Domain is in dotted decimal format. E.g. mydomain.tld, foo.bar.baz.net etc.

User is more complex, it can be either a quoted string or a dotted format with content between the dots and an option + marker.

Example1:
"@@pen00tje"@superuser.com
The user is @@penootje. the domain is superuser.com. There is an @ between the username and the domain.

Example2:
Jane.Smith@Superuser.com

Example3:
Jane.Smith+tag@Superuser.com
This will be mailed with delivery destination Jane.Smith@Superuser.com but to "To:" field will contain Jane.Smith+tag@Superuser.com. This allows your mail client to filter on messages.

Invalid example:
Jane..Smith@Superuser.com (two dots are not allowed un the username)

Most web forms seem to get this wrong. Even large organisations sunch as Dell and Visa fail when they encounter legal but unexpected email addresses. Please do read the RFC. At first it is not trivial to read what is written, but it already comes in a format which is perfect for translating to code (or excel rules). and it is the one source of truth for what is allowed.

| improve this answer | |
  • 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) – Andi Mohr Jul 13 '16 at 14:55
  • 1
    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. – Hennes Jul 13 '16 at 15:38
  • 1
    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. – Hennes Jul 13 '16 at 21:43
0

=AND(NOT(ISERROR(FIND("@",A2))),NOT(ISERROR(FIND(".",A2))),ISERROR(FIND(",",A2)),ISERROR(FIND(" ",A2)),RIGHT(A2,1)<>".",ISNUMBER(MATCH("@.?*",A2,0)))

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.