Find forumla for Excel

Posted on 2011-10-27
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 --

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

is what im asking possible ?

Question by:justin33234
    LVL 44

    Expert Comment

    I noticed that you have both CA and INFO in your list.  They are both valid high level domain names.
    LVL 44

    Accepted Solution

    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.


    Open in new window

    I then code the following in A1

    Open in new window

    This will either result in TRUE or #N/A value.
    LVL 50

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    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,…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now