2

I would like to check if anyone can help with the below.

I would like to have a validation formula for email addresses. After combing through the internet and other threads, I found something that works.

However, I'd like the data validation to check for comma and flag that as error too. The current formula only flag spaces.

Any advice/suggestion to tweak this formula?

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))
syam
  • 799
  • 1
  • 12
  • 30
Wayne
  • 21
  • 1
  • 2
  • Validating an email can be tough. With Excel formula without VBA it would be a nightmare, if it is done correctly. In general, other languages use RegEx for the validation - https://stackoverflow.com/questions/201323/how-to-validate-an-email-address-using-a-regular-expression – Vityata Mar 13 '18 at 09:53
  • @Vityata: "Validating an email can be tough.": No, it is really easy because the only thing you really can say about a email address is: It must contain the `@` character. Your linked answer shows a really good regexp but I bet even this excludes some strange but valid email addresses. – Axel Richter Mar 13 '18 at 10:20
  • Do you need it to be valid? Or do you need it to be both valid and correct? If the latter, set up a system that includes sending/receiving an activation email. – Ron Rosenfeld Mar 13 '18 at 10:28
  • Hi, thanks for the responses. I’m putting this as a data validation for front end user input and since macros are disabled by default, if the user does not allow macro to run, validation can’t work. Therefore, I’m trying to do simple elimination without vba or other coding. We do have a system for sending / receiving activation email but that will come in later in the process. Appreciate any other comments / feedback! – Wayne Mar 14 '18 at 03:18

1 Answers1

0

To "tweak" your formula to also check for a comma, you can use an array constant:

EDIT: to correct formula to allow for its use in Data Validation (remove array constant)

=AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2)),ISERROR(OR(FIND(" ",A2),FIND(",",A2))))

I provide this only as a method of showing one way of testing to exclude multiple characters from a string. This is NOT, in my opinion, a good way to validate an email address.

Note that your formula, if it does NOT find an "@" or a ".", will return a #VALUE! error. If you would rather it return FALSE, suggest you wrap those FIND's in an ISNUMBER function.

However, and this is my opinion only, I think the best way to ensure that an email address is both valid and correct is to use a system which includes an activation email. That can avoid both typos and malformed data.

Of course, your formula will allow certain types of invalid emails. For example, it does not test that the @ is in the middle of the string, nor the dot. Nor that the dot precedes the @. etc, etc, etc

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Your formula excludes `"Jane Doe"@example.com` and `"Doe,Jane"@example.com` which both are valid email adresses. Also there is no must have for having a dot somewhere in an email address. – Axel Richter Mar 13 '18 at 11:03
  • @AxelRichter This is not **MY** formula for validating email addresses. I thought I was pretty clear that I was only tweaking the OP's formula, as he had requested, but that I did not think it was a good method. It will also validate email's of the type `@jane.doe` which is not valid, and fail to validate many other compliant addresses. I will add some further commentary to clarify. – Ron Rosenfeld Mar 13 '18 at 11:07
  • Hi, thanks for your help. So far in my experience, I’ve not seen email addresses with spaces or commas. What I’m trying to do is eliminate *common* mistakes like missing out the @ and . and typos because , and . is close to each other. Could you help me with the ISNUMBER formula you suggested? I am really noob at the more advanced Excel formulas (as you can tell I can’t even tweak a formula on my own). Thank you very much! – Wayne Mar 14 '18 at 03:25
  • I just tried to put the tweaked formula as the data validation formula but I received an error message — “you may not use reference operators such as unions, intersections, and ranges or array constants for data validation criteria”. Is there another way? Thanks in advance! – Wayne Mar 14 '18 at 03:43
  • @Wayne One needs to merely use an `OR` function instead of the array constant. See the edit. – Ron Rosenfeld Mar 14 '18 at 10:07