Solved

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

Posted on 2016-09-01
12
55 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 43
InStr Function not working properly in macro 3 19
ActiveX Listbox Multi Select in Excel 2010 8 19
the code is not looping through 11 35
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

895 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

11 Experts available now in Live!

Get 1:1 Help Now