Solved

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

Posted on 2016-09-01
12
59 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 29

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 18

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 29

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 29

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 18

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 29

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 18

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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