Posted on 2011-04-29
Last Modified: 2012-05-11
I have an Excel sheet that I need to do some custom filtering, here's what I am trying to do:

I have a report that has specific data in Column A, In column D I have stacks of numbers in each cell that are associated with the number in Column A.

I am trying to remove the sets of numbers from Column D  that I don't need. The easiest was to separate these is by number of characters. The numbers I need to keep are US####### (US and 7 digits).

I have tried filtering, and it only reads the first number in a cell, if it doesn't meet the filter it's not shown. Even if the number below it (in the same cell) matches the filter.

So here is the big question: How do I tell Excel to look in column D and only keep all instances of US#######, while removing anything else. And doing do to the entire cell, not just the first entry of the cell.

I hope I made sense, my brain hurts at this point!
Question by:Majo2469
    LVL 9

    Accepted Solution

    Sort the data based on column D.  Select everything that is not US and delete it.  Should be simple.  Why are you making it complicated?

    Unless you are doing it with a macro ...

    LVL 31

    Assisted Solution

    by:Rob Henson
    If you are using Auto Filter you can use the Custom option and choose Equals to and enter US??????? into the entry box.

    This will show all entries that begin with US and have specifically seven characters after.

    If you want to show all rows that don't meet that criteria then use the dropdown in Custom filter for "does not equal" and enter the same.  You can then delete the rows that are shown.

    Rob H

    Assisted Solution

    If I understand you post correctly, I think I found an answer for you at Mr., posted by Tom Urtis

    While this particular post worked when I tested it on Excel 2007, it should also work for you.  I don't have Excel 2010, but the steps outlined below (based on the above link) should help you in the right direction.

    Excel 2007 steps:

    1. Select the column to be filtered
    2. On the Data tab, select Filter
    3. Left-click on the Filter button at the top of the column.
    4. In the submenu select Text Filters Custom Filter...
    5. In the Custom AutoFilter window, in the left box select "equals". In the right box type in ????????? or as many as you want for the number of characters to filter.  9-? will filter (show) only rows with 9 characters in the cell, which in your case should be any cell that contains US followed by 7 numbers.

    I hope this helps.
    LVL 29

    Assisted Solution

    Use this formula in Column E and copy to each row with coresponding data in column D


    Filter on E for 1s.
    LVL 24

    Expert Comment

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

    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

    This article will show you how to use shortcut menus in the Access run-time environment.
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    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.

    733 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