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!!!!

Not 100% sure about your need, but see if Data Validation gives you what
you want.

Regards,
Ryan--

--
RyGuy


"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!!!!

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!!!!

Yes - I am trying 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 the length LE 32.
Any ideas?

"ryguy7272" wrote:

Not 100% sure about your need, but see if Data Validation gives you what
you want.

Regards,
Ryan--

--
RyGuy


"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!!!!

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!!!!

Here is one way.

1. Click FormatConditional Formatting on the menu bar
2. Choose "Formula Is" and paste this in the formula box:

=SEARCH("@",A1,1)0

3. Click "Pattern" and choose whatever color you want

This simply does an unintelligent search of A1 to see if contains the
"@" character. You could also use this in the DataValidation box for
the same effect.

HTH,
JP

On Jan 17, 12:23*pm, 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!!!!

A **total** length of 8... including the @ sign? If so, change your
condition formula to this...

=ISNUMBER(--LEFT(C3))*(LEN(C3) =8)*(NOT(ISERROR(FIND("@",C3))))

Rick


"Theo" wrote in message
...
Yes - I am trying 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 the length LE 32.
Any ideas?

"ryguy7272" wrote:

Not 100% sure about your need, but see if Data Validation gives you
what
you want.

Regards,
Ryan--

--
RyGuy


"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!!!!

Worked perfectly!!
Thanks so much
T

"JP" wrote:

Here is one way.

1. Click FormatConditional Formatting on the menu bar
2. Choose "Formula Is" and paste this in the formula box:

=SEARCH("@",A1,1)0

3. Click "Pattern" and choose whatever color you want

This simply does an unintelligent search of A1 to see if contains the
"@" character. You could also use this in the DataValidation box for
the same effect.

HTH,
JP

On Jan 17, 12:23 pm, 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!!!!