Results 1 to 5 of 5

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

This is a discussion on How to control (string text) data entry in cell with Data Validation within the Excel Questions forums, part of the Question Forums category; Hi All in Excel World, Really hoping someone can help. In my spreadsheet I want to use (cell J2) Data ...

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    89

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

    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.

  2. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,462

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

    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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    89

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

    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?

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,462

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

    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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    89

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

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com