Home

how to validate that email address format is correct or not in xls

%3CLINGO-SUB%20id%3D%22lingo-sub-113705%22%20slang%3D%22en-US%22%3Ehow%20to%20validate%20that%20email%20address%20format%20is%20correct%20or%20not%20in%20xls%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113705%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%2C%20I%20want%20to%20validate%20the%20below-given%20email%20address%20format%20in%20xls.%20if%20the%20format%20is%20correct%20which%20means%20yes%20then%20I%20have%20to%20update%20the%20value%20with%20respect%20to%20the%20email%20id%20as%20true%20else%20false.Can%20anyone%20pls%20help%20me%20with%20the%20correct%20formula%20or%20process.%3C%2FP%3E%3CP%3Ecaria.matt%40zew.de%3C%2FP%3E%3CP%3Ed.gibons%40lse.ac.uk%3C%2FP%3E%3CP%3Eweink%40ese.eur.nl%3C%2FP%3E%3CP%3Emark%40york.ac.uk%3C%2FP%3E%3CP%3Ejoa%40gmail.com%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-113705%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EEmail%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113874%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20validate%20that%20email%20address%20format%20is%20correct%20or%20not%20in%20xls%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113874%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20depends%20on%20how%20do%20you%20consider%20if%20the%20format%20is%20correct.%20If%3C%2FP%3E%3CP%3E-%20it%20shall%20be%20%22%40%22%20AND%3C%2FP%3E%3CP%3E-%20at%20least%20one%20dot%20(%22.%22)%20after%20the%20%22%40%22%3C%2FP%3E%3CP%3Ewhen%3C%2FP%3E%3CPRE%3E%3DISNUMBER(SEARCH(%22%40%22%2CA1))*ISNUMBER(SEARCH(%22.%22%2CA1%2CSEARCH(%22%40%22%2CA1)))%3C%2FPRE%3E%3CP%3Ewhich%20returns%201%20or%200%20(other%20words%20TRUE%20of%20FALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
sumitctm
Frequent Visitor

 

 

Actually, I want to validate the below-given email address format in xls. if the format is correct which means yes then I have to update the value with respect to the email id as true else false.Can anyone pls help me with the correct formula or process.

caria.matt@zew.de

d.gibons@lse.ac.uk

weink@ese.eur.nl

mark@york.ac.uk

joa@gmail.com

1 Reply

Hi,

 

It depends on how do you consider if the format is correct. If

- it shall be "@" AND

- at least one dot (".") after the "@"

when

=ISNUMBER(SEARCH("@",A1))*ISNUMBER(SEARCH(".",A1,SEARCH("@",A1)))

which returns 1 or 0 (other words TRUE of FALSE)

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies