Hi All in Excel World,
Really hoping someone can help.
In my spreadsheet I want to use (cell J2) Data Validation for email address input -to ensure only acceptable string text can be entered, e.g. "(.)", "(@)", "(.co.uk)", "(.com)" etc. There are a total of 6 email address extensions I'd like to allow in the formula.
I've tried various combinations (using Data Validation) of: IF, SEARCH and MATCH (using a combination of wild cards and text) but no joy.
Example.
=IFERROR(IF(SEARCH("*"&"."&"*"&"@"&"*"&".com"&"*"&".co.uk"&"*"&".net"&"*"&".org"*"&".org.uk"*"&".gov.uk",J2),"Correct Format",),"Wrong Format")
=IFERROR(IF(SEARCH("*"&"."&"*"&"@"&"*"&".com"&"*"&".co.uk"&"*"&".net"*"&".org"*"&".org.uk"*"&".gov.uk",J2),"Correct Format",), "Wrong Format")
=ISNUMBER(MATCH("*@*.???"&"*"&"."&"*"&"@"&"*"&
Any help on this would be greatly appreciated.
Thanks in advance. :wink:
Really hoping someone can help.
In my spreadsheet I want to use (cell J2) Data Validation for email address input -to ensure only acceptable string text can be entered, e.g. "(.)", "(@)", "(.co.uk)", "(.com)" etc. There are a total of 6 email address extensions I'd like to allow in the formula.
I've tried various combinations (using Data Validation) of: IF, SEARCH and MATCH (using a combination of wild cards and text) but no joy.
Example.
=IFERROR(IF(SEARCH("*"&"."&"*"&"@"&"*"&".com"&"*"&".co.uk"&"*"&".net"&"*"&".org"*"&".org.uk"*"&".gov.uk",J2),"Correct Format",),"Wrong Format")
=IFERROR(IF(SEARCH("*"&"."&"*"&"@"&"*"&".com"&"*"&".co.uk"&"*"&".net"*"&".org"*"&".org.uk"*"&".gov.uk",J2),"Correct Format",), "Wrong Format")
=ISNUMBER(MATCH("*@*.???"&"*"&"."&"*"&"@"&"*"&
Any help on this would be greatly appreciated.
Thanks in advance. :wink: