How to control (string text) data entry in cell with Data Validation

wcm69

Board Regular
Joined
Dec 25, 2016
Messages
112
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:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Keep in mind that DV will only test a cell for set criteria, and then either allow or deny data entry, it will not "put" anything into a cell. It can be set up so that if entry is denied, a message pops up saying whatever you set it to, but that message also will not be entered into the cell.

Also, just like Conditional Formatting, DV works only of TRUE (1) or FALSE (0), so you just need to structure the rules to return 1 of those
 
Upvote 0
Thanks FDibbins,

I don't so much need the message to display in the cell via the DV. What I want is when an email address is put in Cell J2 if it doesn't meet or follow the correct email format then the DV returns an error message or if correct the email address is allowed in the cell.

My main trouble is the correct function to use and the format in which it is written - to enter into the DV.

Can you help any further with this?
 
Upvote 0
I can try.

So you need to text for (must contain).....
.
@
.co.uk
.com
....what else?

Whenever I need to construct a complex DV or CF rule, I construct it IN the worksheet 1st, that makes it easier to play with and adjust to make sure I am getting what I need
 
Upvote 0
Many thanks,

I'm actually trying to construct a crude email address validation formula for my contacts database. So I'm trying to capture the various (common) UK email address extensions. In this way I hope to prevent (as far as possible) any incorrect (email address) entries.

The conditions I want to set for the entries into Cell J2 are - the email address MUST Have:

"@", and period (.), and not contain any spaces in the (text) entry.

Then the allow following (string text) address extensions only:
".com", ".co.uk", ".net", ".org", ".org.uk" and ".gov.uk".

Any help is appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top