Replacing Abbreviations in Street Addresses

[belltt]

I came accross this thread today:

http://www.excelforum.com/excel-work...addresses.html

The Macro provided by JBeaucaire on 1-26-2010 works great for what I'm trying to accomplish.....but I'm running into two issues and I'm wondering if there is an easy fix for them:

1.) I'm unable to replace punctuation/symbols (e.g. # , .) if they have a character immediately preceding or following....I have them listed in my column A but the macro doesn't recognize them and perform the replace unless the punctuation has a leading and following space

2.) If the list of addresses selected exceeds 65,000 the macro returns "0" for all addresses in the list......is there a way to expand the number of addresses selected for the macro run?

Any help would be apreciated. Thank you.

[JBeaucaire]

1) The macro is designed to replace words, not characters within words. After the macro expands the words the way it was originally designed, run a REPLACE function on the selected cells to fix individual characters.

2) I just ran the macro on 65536 selected cells and it worked fine. Just one column at a time.

[belltt]

Thanks for the reply. Yes, on a second look it runs fine for me up to 65,536 records (not 65,000 records as I had previously stated)....but does not run correctly on colums with > 65,536 addresses.......any specific reason that it wouldn't handle more than 65,536 records?

[JBeaucaire]

Not off hand, but then again, you can simply select smaller sets of data and run the macro, if necessary, yes?