How to clean email addresses?

[msantoso]

Hi 

I have asked for some help last week about email addresses and CharlieS gave me a Regex formula that covers 85% of the syntax errors I could meet. 

here is the great Regex formula

([\w.-]+\@[\w.-]+\.\w{2,4})

However it rejects some email addresses which are correct

(i) Some emails have two dots in their domain name or a dash, which are both accepted. 

Examples: 

j.haber@vbv.bwl.de

office@scheuch.co.at

info@saint-gobain.com

(ii) Some emails have been set as FALSE for some reasons I do not understand

info@Kavitation24.de‬‬  (digits in the domain name?)

kita-sonnenschein-nahrstedt@gmx.de

Woelk&Partner@online.de (ampersand is allowed in email syntax)

do you know how I can get them set as correct addresses? 

thank you 

Myriam

[JohnJPS]

There are some sites with lists of RegEx's to try, notably http://emailregex.com and  https://stackoverflow.com/questions/201323/how-to-validate-an-email-address-using-a-regular-expressi....you could try some of those to see if they include more valid ones.  Lots of good discussion at StackOverflow also.

[msantoso]

thank you John! I visited the sites: I know what my day will be like tomorrow!! 

thank you

myriam

[NJT]

What did you end up going with? I've been using this REGEX_Match([Email_address_1], '.+@.+\..+') but it lets through emails with spaces in them which I could remove but I recently read that spaces can be allowed in an email address in some instances. But Salesforce.com doesn't allow them when I send them data. I wonder what their validation process is based on. 

I found myself in a rabbit hole on the StackOverflow discussion and then Dominic Sayers blog seemed pretty good but then how to incorporate his PHP program into Alteryx work flow... Anyway curious where you ended up with your email syntax validation. I was just looking for basic syntax check to remove things like noemail and NA and all that when I started this project.