data validation error

[ushaanu]

hi all,

I have one excel sheet I need one particular cell like A2 when some one enter email address and by typing mistake not mentioning @ sing so cell not allowed to proceed further and gives error .

thanks
Anu

 

[Nebu]

May be something like this in custom data validation of cell A1,
=SEARCH("@",A1)<>0

 

[Syedali]

Hi,
PFA
For your Query am Trying. Instead of this I have used Conditional formatting for not containing "@" means it will highlight.
Let me know if you have any clarfication

 

[Syedali]

May be something like this in custom data validation of cell A1,
=SEARCH("@",A1)<>0

Hi Nebu,
It is not working.

 

[Nebu]

Oops forgot to put the start number it should be =SEARCH("@",A1,1)<>0, it will work fine now

 

[Syedali]

 

[ushaanu]

thank you sooo much....

 

[ushaanu]

Oops forgot to put the start number it should be =SEARCH("@",A1,1)<>0, it will work fine now

hi Nebu

I need one more thing in one cell if I am type in lower or upper case automatically word change in to proper case.

thanks
Any

 

[Khalid NGO]

Hi Anu,
Good day...

Not sure it is possible in DV.
But this macro can do what you want:

Sub lower_case()
    Dim Rng As Range
    Dim c As Range
    Set Rng = ActiveSheet.Range("A1:A20")
    For Each c In Rng
        c.Value = WorksheetFunction.Proper(c.Value)
    Next c
End Sub

Regards,

 

[Khalid NGO]

Using Nebu's approach you can also restrict text to be written proper only along-with @

=AND(SEARCH("@",A1,1)<>0,EXACT(A1,PROPER(A1)))