+ Reply to Thread
Results 1 to 4 of 4

Custom Filter / Advance Filter - "Begins with..." - Case Sensitive!

  1. #1
    Registered User
    Join Date
    01-13-2006
    Posts
    20

    Custom Filter / Advance Filter - "Begins with..." - Case Sensitive!

    Hi All,

    I have a list of 35,000 accounts, with details of name, contact phone, residential address, postal address, etc as the column headers/fields.

    As part of tidying up our database I need to fix up silly things like having "Po Box 1234" listed as the residential address instead of "PO Box 1234".

    I've had a look at the EXACT function and custom filters (ie using wildcards such as '?' and '*'), however can't figure out how to combine the two. Basically, I want to filter so that I only see records that BEGIN WITH "Po" - I do not want to see records that begin with "PO".

    Any advice on how to tackle this?

    Had a bit of a search of the forums but couldn't find what I was after - if the info is in another post please feel free to direct me that way and I'll close this thread.

    Cheers!

    Rob.

  2. #2
    Registered User
    Join Date
    01-13-2006
    Posts
    20

    Re: Custom Filter / Advance Filter - "Begins with..." - Case Sensitive!

    Additional Info:

    I have found a bit of a 'clumsy' workaround by adding an extra column and running the following formula in it:

    =IF(EXACT((LEFT(C2,2)),"Po"),"Check","")

    This looks at the first two characters of the Postal Address field and tells me to check if they match "Po" exactly. I can then filter by this to show me only non-blank rows.

    Thought there might be an easier/cleaner way - would still love to hear if someone has a better idea.

    Cheers!

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Custom Filter / Advance Filter - "Begins with..." - Case Sensitive!

    No easier way. I just came up with the helper column idea as well, but my formula would be

    =EXACT(LEFT(A5,2),"Po")

    which returns TRUE or FALSE respectively, so you could filter by that.

    You could also try to run a Find and Replace, searching for "Po " (note the blank!) and replacing it with "PO ". You may want to run a search without replace first, to see if you have any other text that fits the pattern. If not, you can do a Replace All, otherwise you'll have to click for each result found.

    PS: or simply do Find and Replace for "Po box" replace with "PO box". That worked beautifully on my 20 records sample.
    Last edited by teylyn; 09-07-2009 at 12:59 AM. Reason: added new idea

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Custom Filter / Advance Filter - "Begins with..." - Case Sensitive!

    FWIW you can do it with Advanced filters using a formula like the one you mentioned, but there isn't a direct way to do it with the Autofilter without the additional column.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1