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

**Rob Moyle** · 09-07-2009, 12:31 AM

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.

**Rob Moyle** · 09-07-2009, 12:42 AM

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!

**teylyn** · 09-07-2009, 12:56 AM

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.

**romperstomper** · 09-07-2009, 06:28 AM

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.