Conditionally edit cells in an Excel range using VBA
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.
Advice?
Advice?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
=PO_Boxer(A1)
Brad
PO-Boxer-Q27732653.xls
=PO_Boxer(A1)
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 FunctionBrad
PO-Boxer-Q27732653.xls
ASKER
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.
ASKER