Solved

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

Posted on 2016-09-01
12
53 Views
Last Modified: 2016-09-22
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
Comment
Question by:cansevin
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 125 total points
ID: 41780885
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41781027
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
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 125 total points
ID: 41781037
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41781042
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781054
@ 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781056
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41781080
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 41781404
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41801355
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41801777
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now