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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies