Jul 08 2019 06:56 AM
Hello
I have a large spread sheet which includes 100's of emails, often if the email address is unknown someone will write random text which is quite frustrating, I have tried using custom data validation to stop someone from being able to enter text which doesn't include a @ but I'm getting a bit stuck. Can anyone advise me of the correct formula I should be using.
Thanks in advance.
Jul 08 2019 08:10 AM - edited Jul 08 2019 08:10 AM
Hi Sarah,
you can use the following userdefined function. See attached file too.
Function Mailcheck(strText As String) As Boolean
Dim objRegEx As Object
Dim objMatch As Object
Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
.Global = False
.Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" 'gängige e-Mail-Adressen prüfen
.MultiLine = True
Set objMatch = .Execute(strText)
If objMatch.Count <> 0 Then
Mailcheck = True
Else
Mailcheck = False
End If
End With
Set objRegEx = Nothing
Set objMatch = Nothing
End Function
Best regards
Bernd
Jul 08 2019 08:18 AM
Please try the following in data validation custom formula
=ISNUMBER(MATCH("*@*.???",C2,0))
Ps: Please replace C2 with the cell reference of the first row of email list