[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Find forumla for Excel

Posted on 2011-10-27
5
Medium Priority
?
236 Views
Last Modified: 2012-05-12
I am trying to filter out bad data in a spreadsheet full of emails, looks like we got hit with some spammers to one of our sites and flooded our email lists with random junk, what I'd like to do is make a forumla, that i can put in row A column 1, then copy it down and it'd flag any emails that do NOT contain a valid domain.

basically if column a contains .com, .org, .net, .??, etc etc
full list --  http://data.iana.org/TLD/tlds-alpha-by-domain.txt

so it'd flag a valid entry with the number one, anything else would be blank.

is what im asking possible ?

Thanks.
0
Comment
Question by:justin33234
  • 2
3 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 37040797
I noticed that you have both CA and INFO in your list.  They are both valid high level domain names.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 37040969
Never mind.  I understand why you posted that.

I imported that text into one of my worksheets and added a column of "TRUE" values.  I named this range ValidTLDs.

AC	TRUE
AD	TRUE
AE	TRUE
AERO	TRUE

Open in new window


I then code the following in A1
=VLOOKUP(MID(B1,LOOKUP(2^15,FIND(".",B1,ROW(INDIRECT("1:"&LEN(B1)))))+1,255),ValidTLDs,2,FALSE)

Open in new window


This will either result in TRUE or #N/A value.
0
 
LVL 50
ID: 37419412
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

873 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