Custom Format for email addressses (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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:

=NOT(ISERROR(SEARCH(".",B1,SEARCH("@",B1)+1)))

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.

Reply | Quote

Thank you thank you thank you, Hans

That worked wonderfully …
–cat

Reply | Quote