If cell isn't blank, then "Yes" if there is somthing in it, then "No"

Confirming only "@" character in a cell doesn't mean that the cell contains an valid email address.

You may try the following UDF (User Defined Function) to validate if a cell contains an email.

To implement the UDF, follow these steps...

1) Open your workbook.
2) Press Alt+F11 to open VB Editor
3) On VB Editor --> Insert --> Module
4) Paste the code given below into the opened code window.
5) Close the VB Editor
6) Save your workbook as Macro-Enabled Workbook.

Now you may use the function CheckForAnEmail like this.....
Assuming your string is in D2, then try this....

In F2

=CheckForAnEmail(D2)

Select all Open in new window

UDF:

Function CheckForAnEmail(str As String) As String
Dim regEx As Object
Dim sPattern As String

Set regEx = CreateObject("VBScript.RegExp")
sPattern = "[A-Za-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-zA-Z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
regEx.Pattern = sPattern
If regEx.Test(str) Then
    CheckForAnEmail = "Yes"
Else
    CheckForAnEmail = "No"
End If
End Function

Select all Open in new window