Home

Data Validation to force user to add correct email address

%3CLINGO-SUB%20id%3D%22lingo-sub-742542%22%20slang%3D%22en-US%22%3EData%20Validation%20to%20force%20user%20to%20add%20correct%20email%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742542%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20spread%20sheet%20which%20includes%20100's%20of%20emails%2C%20often%20if%20the%20email%20address%20is%20unknown%20someone%20will%20write%20random%20text%20which%20is%20quite%20frustrating%2C%20I%20have%20tried%20using%20custom%20data%20validation%20to%20stop%20someone%20from%20being%20able%20to%20enter%20text%20which%20doesn't%20include%20a%20%40%20but%20I'm%20getting%20a%20bit%20stuck.%26nbsp%3B%20Can%20anyone%20advise%20me%20of%20the%20correct%20formula%20I%20should%20be%20using.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-742542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742805%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20force%20user%20to%20add%20correct%20email%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372714%22%20target%3D%22_blank%22%3E%40sarahsmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sarah%2C%3C%2FP%3E%3CP%3Eyou%20can%20use%20the%20following%20userdefined%20function.%20See%20attached%20file%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFunction%20Mailcheck(strText%20As%20String)%20As%20Boolean%3CBR%20%2F%3EDim%20objRegEx%20As%20Object%3CBR%20%2F%3EDim%20objMatch%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20objRegEx%20%3D%20CreateObject(%22vbscript.regexp%22)%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20objRegEx%3CBR%20%2F%3E.Global%20%3D%20False%3CBR%20%2F%3E.Pattern%20%3D%20%22%5Cw%2B(%5B-%2B.'%5D%5Cw%2B)*%40%5Cw%2B(%5B-.%5D%5Cw%2B)*%5C.%5Cw%2B(%5B-.%5D%5Cw%2B)*%22%20'g%C3%A4ngige%20e-Mail-Adressen%20pr%C3%BCfen%3CBR%20%2F%3E.MultiLine%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20objMatch%20%3D%20.Execute(strText)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objMatch.Count%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EMailcheck%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3EMailcheck%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20objRegEx%20%3D%20Nothing%3CBR%20%2F%3ESet%20objMatch%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethe%20VBa-Tanker%20-%20a%20database%20full%20of%20usefull%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742827%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20force%20user%20to%20add%20correct%20email%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372714%22%20target%3D%22_blank%22%3E%40sarahsmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20the%20following%20in%20data%20validation%20custom%20formula%3C%2FP%3E%3CP%3E%3DISNUMBER(MATCH(%22*%40*.%3F%3F%3F%22%2CC2%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPs%3A%20Please%20replace%20C2%20with%20the%20cell%20reference%20of%20the%20first%20row%20of%20email%20list%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
sarahsmith
Occasional Visitor

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 Replies

@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

@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 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies