I was hoping to use data validation, but struggling with the format.
I have this for a custom validation:
=AND(LEFT(C3, 1) ="#",LEN(C3) =8)
This requires a # and total length of 8.

So I was hoping to use something like the above, except requiring the "@"
somewhere WITHIN and a length LE 32.
Any ideas?

"Mike H" wrote:

Hi,

You could right click the sheet tab, view code and paste this in. If an
invalid emal address is entered in the range A1 - a10 (Change to suit) you
get a popup warning

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Dim RE As Object
Set RE = CreateObject("vbscript.REgExp")
RE.Pattern = "^[a-zA-Z0-9\._-]+@([a-zA-Z0-9_-]+\.)+([a-zA-Z]{2,3})$"
If RE.test(Target.value) = False Then
MsgBox "Invalid email in " & Target.Address
End If
Set RE = Nothing
End If
End Sub

Mike

"Theo" wrote:

I want to be sure that the entry into a cell is an email address - is there a
way to ensure that at least one character (any character) is an at sign (@).
Any ideas appreciated!!!!