Data Validation to force user to add correct email address

[sarahsmith]

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.

[#2]

@sarahsmith 

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

the VBa-Tanker - a database full of usefull macros

[#3]

@sarahsmith 

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