Skip to content

Instantly share code, notes, and snippets.

@imamuddinwp

imamuddinwp/Excel Email Validation with Data Validation

Created February 20, 2021 08:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save imamuddinwp/aff4efdd7306df1645b7ab65c1542e13 to your computer and use it in GitHub Desktop.
Save imamuddinwp/aff4efdd7306df1645b7ab65c1542e13 to your computer and use it in GitHub Desktop.
Download ZIP
Excel Email Validation with Data Validation; Powered by Imam Uddin, imamuddinwp
Raw
Excel Email Validation with Data Validation
# Get Email from raw data:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",LEN(A2))),LEN(A2)))
#Get Varified Email:
=AND(ISERROR(FIND(" ",B2)),LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,IFERROR(SEARCH("@",B2)<SEARCH(".",B2,SEARCH("@",B2)),0),NOT(IFERROR(SEARCH("@",B2),0)=1),NOT(IFERROR(SEARCH(".",B2,SEARCH("@",B2))-SEARCH("@",B2),0)=1),LEFT(B2,1)<>".",RIGHT(B2,1)<>".")
@imamuddinwp
Copy link
Author

Tags:
excel validate email address formula,
excel conditional formatting email address,
andfinda2find a2iserrorfind a2,
how to filter email addresses in excel,
clean emails in excel,
find email address in excel,
how to find invalid email id,
remove invalid emails

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment