Conditionally edit cells in an Excel range using VBA

David L. Hansen
David L. Hansen used Ask the Experts™
I have a list of P.O. Boxes and I need to detect and remove duplicates.  Of course the column contains P.O. Boxes with all sorts of formats (ie. "po box," "p.o. Box," "Po Box," "P.O. Box," "P. O. Box," etc.).  If I normalize all of them to the same format I will easily be able to remove the dups.  I thought I'd first remove all periods in the column, then apply UPPER to get them all to uppercase.  Then remove all spaces and look for dups.  I would probably add a space back between the "O" and the following numbers.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
One quick way would be to just isolate the number itself, since you don't care about the PO Box, regardless of the format it is in.

using your idea of UPPER ... you can isolate the Number of the PO box with the following formula:
(supposing existing data starts in column A, row 1)


Fill that formula down for the entire column... then you can remove dups based on this new column.
I just created a helper column which uses two substitute formulas, they are nested in an upper; then I used conditional formatting to show the dups.  At this point I just need to blow away the duplicates (without having to manually do it).
On the data tab of excel 2010.... there is a remove duplicates button... that sounds like it would do what you want it to do.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

You might want to consider using a user-defined function to standardize your PO Boxes. You install the code in a regular module sheet (just like a macro), then use it with a formula like:

Function PO_Boxer(s As String) As String
Dim RgExp As Object
Set RgExp = CreateObject("VBScript.RegExp")
With RgExp
    .Pattern = "([Pp])(\s*)(\.?\s*)([Oo])(\s*)(\.?\s*)([Bb][Oo][Xx])(\s*)(\d+)"
    .Global = True
    PO_Boxer = .Replace(s, "P.O. Box $9")
End With
Set RgExp = Nothing
End Function

Open in new window

I found a different formula that took care of my problem.  I still want to award points to the expert though because his formula would have worked just as well I believe.  He also gave me a much needed tip at the end.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial