Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Format for email addressses (Excel 2003)

    I wish to force a cell to contain an email address. So I would like to create a custom format that does something like this:
    A = either character or number, C= character, @=@, . = .

    AAAAAAA@AAAAA.CCC

    Can someone help me do this?
    Thanks,
    --cat

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Format for email addressses (Excel 2003)

    Formatting a cell like that would NOT force the cell to contain an email address, it would just make the cell display something that looked like an email address. Do you really want to use Data Validation to actually force the contents to be an email address?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Format for email addressses (Excel 2003)

    I thought I could use a custom number format ( format | cells | number tab | customer )t
    to ensure that what is typed in the cell is actually an email address (with the @ sign and the "dot with 3 characters after it).
    I don't think I can use Data Validation for this.

    Any other suggestions ... ? :-)
    --cat

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Format for email addressses (Excel 2003)

    A format only determines how the cell is displayed, not what the user can enter. You *can* use validation:
    Say that the cell is B1.
    Select B1.
    Select Data | Validation...
    Select Custom from the Allow dropdown.
    Enter the following formula in the Formula box:
    <code>
    =NOT(ISERROR(SEARCH(".",B1,SEARCH("@",B1)+1)))
    </code>
    Make sure that Ignore blanks is checked.
    Switch to the Error Alert tab.
    Make sure that the Stop style is selected.
    Enter an appropriate error message.
    Click OK.

    Note: the formula does not check for exactly 3 characters after the dot. In my country, for example, web and e-mail addresses end in .NL.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Format for email addressses (Excel 2003)

    Thank you thank you thank you, Hans

    That worked wonderfully ... :-)
    --cat

Posting Permissions

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