Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-01
12
Medium Priority
?
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 500 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 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 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 22

Assisted Solution

by:Roy Cox
Roy Cox earned 500 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
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.

 
LVL 48

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 33

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
 
LVL 33

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 22

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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 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 33

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 22

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

610 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