Find and Replace Multiple Text String VBA

**maacmaac** · 12-28-2011, 08:45 AM

In column C I have PO Box Addresses I need to normalize such as:

P.O. Box 12345
PO Box 12345
P. O. Box 12345
POB 12345
box 12345
BOX 12345

I need to normalize the data by using find and replace

Replace "P.O. Box " with "PO Box ", etc as example. Is there a way to find all combinations to replace in one statement without having to write multiple statements? The only requirement is it must not be case sensitive (i.e. strings "box " and "BOX " are the same)

This is a partial list of replacements (I want to replace all with "PO BOX ")
"P O Box "
"POB "
"BOX "
"box "
"Pob "
"pob "
"P.O. BOX "

I have this so far:

Please Login or Register  to view this content.

**Bob Phillips** · 12-28-2011, 08:55 AM

Please Login or Register  to view this content.

**Firefly2012** · 12-28-2011, 08:59 AM

Looks to me like your best bet would be to simply extract all the digits in the cells (eg 12345) and then replace the contents with your normalised string "PO Box" concatenated to the digits (this does of course assume your PO Box numbers are wholly composed of numeric characters):

Please Login or Register  to view this content.

**maacmaac** · 12-28-2011, 09:01 AM

Is there a way to ignore "case" sensitive strings. For example for string "Box ", there are cases of BOX, Box, box, BOx, bOX, etc. I am trying to avoid having to write a case statetment for each possibility. thanks

**Firefly2012** · 12-28-2011, 09:05 AM

Assuming the Box numbers are all numeric then the code I posted should work

**maacmaac** · 12-28-2011, 09:07 AM

Firefly,

Thanks for code but it won't work as the file is composed of other addresses besides the Po boxes. I.e. there are other address types in same column such as 123 Smith Avenue. Thanks anyhow.

**maacmaac** · 12-28-2011, 11:00 AM

I was able to figure out a solution if multiple cases of upper/lower case combinations by using UCase solution.

Replaced

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Find and Replace Multiple Text String VBA

LinkBack

Thread Tools

Rate This Thread

Display

Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Re: Find and Replace Multiple Text String VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions