We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
14 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
If cell isn't blank, then "Yes" if there is somthing in it, then "No"
Posted on 2016-09-01
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
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
[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
Active today
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")
=IF(ISERR(FIND("@", D2)), "NO", "YES")
Active today
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
UDF:
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)
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
Active today
Here's anothe rFunction that tests for a valid email address
In the sheet use like this
=IF(ValidEmail(D1),"Yes","
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
In the sheet use like this
=IF(ValidEmail(D1),"Yes","
Active today
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.
Active today
@ 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
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
Active today
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.
Active today
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.
Active today
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
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
Active today
I object as no reason was provided for closing the question and considering OP's following requirement...
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
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
Active today
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.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.