• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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

I need an if then statement that states if there is a value in the cell.

Column D has email addresses in it. Actually some of them have email addresses. In Column F I want it to say "Yes" if there is an email address in the cell and "No" if there is no email address.

THanks!

Chris
0
cansevin
Asked:
cansevin
  • 4
  • 3
  • 2
  • +1
4 Solutions
 
Wayne Taylor (webtubbs)Commented:
I assume they are all "proper" email addresses? If so, you can check for the existence of the @ symbol with this formula...

=IF(ISERR(FIND("@", D2)), "NO", "YES")
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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)

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

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Here's anothe rFunction that tests for a valid email address

Option Explicit

Public Function ValidEmail(pAddress As String) As Boolean
     '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
        ValidEmail = .Test(pAddress)
    End With
    Set oRegEx = Nothing
End Function

Open in new window


In the sheet use like this

=IF(ValidEmail(D1),"Yes","No")
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Wayne Taylor (webtubbs)Commented:
The question wasn't asking to check for valid adresses, but if there was a value in the cell or not. A very simple formula will do that, but I took it a small step further without getting carried away.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Roy
Here is the comparison between both the UDFs.
My UDF returns Yes if a part of string (or whole string) in a cell is a valid email while yours returns Yes if cell contains only a valid email.

I think both the UDFs can be used in different scenarios. Interesting. :)
UDF-Comparison.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Column D has email addresses in it. Actually some of them have email addresses. In Column F I want it to say "Yes" if there is an email address in the cell and "No" if there is no email address.
This is what I read in the post description.
0
 
Roy CoxGroup Finance ManagerCommented:
I'm not clear what is in the cell, whether simply an email address or the email address is part of a string. It would be useful to see an example workbook, but both UDFs may prove useful to others.
0
 
Rob HensonIT & Database AssistantCommented:
Reading the question title, albeit grammatically incorrect, I would say the cells in column D are either blank or contain (something which is supposed to be) an email address.

Therefore, the following answers the question:

=IF(D2<>"","Yes","No")

@cansevin - grammatically incorrect because "isn't blank" is same as "something in it"

Thanks
Rob
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I object as no reason was provided for closing the question and considering OP's following requirement...

Column D has email addresses in it. Actually some of them have email addresses. In Column F I want it to say "Yes" if there is an email address in the cell and "No" if there is no email address.


So when OP didn't respond to any of the experts and it seems all the experts justify their solutions, I propose the question should be closed as follows...

Accepted Solution : Subodh Tiwari (Neeraj) - Post ID: https:#a41781027 - Points 125
Assisted Solution  :  Roy_Cox - Post ID: https:#a41781037 - Points 125
Assisted Solution  :  Wayne Taylor (webtubbs) - Post ID: https:#a41780885 - Points 125
Assisted Solution  :  Rob Henson - Post ID: https:#a41781404 - Points 125
0
 
Roy CoxGroup Finance ManagerCommented:
Confirming only "@" character in a cell doesn't mean that the cell contains an valid email address.

As Subodh said a simple formula will only confirm an entry that contains "@", not necessarily an email address.

Wayne assumes they are valid.

Who can tell for certain if the OP cannot be bothered to reply.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now