Custom Function for OR Function to Test for more than 50 items?

Posted on 2012-08-15
Last Modified: 2012-08-15
I am trying to create a formula to check a column of entries to make sure the entries are either a 2 digit state or the word "foreign".  I am thinking about using the OR function and tagging the items as error if they don't meet any of the items.  The problem is that these entries are copied and pasted from elsewhere so validation is out.  The entries could also have extra spaces which is not so evident, but would cause a problem because this data is used in pivot tables.

Can I create a custom function for this or use some kind of list of data in the OR Function?

Thanks, Eric
Question by:ekaplan323
    LVL 13

    Expert Comment


    Prior to checking it, I would suggest doing a find and replace on that column for spacings.
    LVL 50

    Expert Comment

    by:barry houdini
    "two digit State"?

    Do you have a list of these? Can you supply some examples

    regards, barry

    Author Comment

    Here is a sample of the data.  The states could be any of the postal codes for the 50 states and other than those it coud say foreign.

    LVL 10

    Expert Comment

    If you can create a lookup table of valid entries, you could use Conditional Formatting in the column you're checking.  You'll need to select the column and set the Conditional Formatting after you receive a final version, as pasting will overwrite the format.

    Set this formula in Conditional Formatting rule.  Assumes list of valid entries on Sheet2 in Column A.

    LVL 50

    Accepted Solution

    I listed the State abbreviations in M2:M52 and put "Foreign" in M53, then I used this formula in F8 copied down


    I believe the errors in rows 15 and 17, for example, are caused by some trailing spaces

    See attached

    regards, barry

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    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,…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    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 …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    754 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

    18 Experts available now in Live!

    Get 1:1 Help Now