Quickly Update Exact Values

All,
I have recently found out that my company outsources labels generated from our website, costing us over $300 a month just to have them print the labels. I have volunteered to attempt to take over the process to save money. I have received my first file, and I need to split the full address to separate fields so I can use the mail merge function and format the labels correctly. Here is the quandry...

I know I can use Text to Column, so I created a macro using search and replace for all variences of streets and street abbreviations (like BLVD and DRIVE, and AVE). I was able to insert my special charator for the text to column, however, it isn't an exact search and replace. Now, one of my addresses, for instance, is forrester. Since it includes one of the abbreviations within the name it is inserting the special charactor and will split the actual street name when I do my text to column.

I have been looking and looking, but I can't really figure out how to make an exact search and replace to leave the middle of the names and streets as they are. I am not an avid programmer, so any input would be greatly appreciated.

Many thanks!
Sharon

Could you provide a bit more information?

Maybe a step by step example?

Would like to know an example of one of the cells before you do anything, then know the steps you take in order and what the outcome you are looking for would be versus what the outcome you get.

Yes, here is an example of one of the addresses and exactly what is happening when I try to do what I am trying.

The address block is:
8420 Forrester BLVD Springfield VA 22152

My Macro is simply doing a find and replace on:
BLVD, ST, STREET, ST., AVENUE, AVE., AVE, DRIVE, DR., DR, HWY, HIGHWAY, ROAD, RD, RD. ect. It is replacing with the same value but with a special charator to show where to preform text to column (>)

When I run the macro this address turns into:
8420 ForreSt>er BLVD Springfield VA 22152

This will break the street name, not to mention if the city includes it as well. I need to do an exact find and replace to avoid this, or so it would seem, but I might be way off.

Many things, please let me know if you need more info.

There are probably numerous solutions, and someone will probably give you a bettter one but here is what I would do:

1. Use text to columns to break apart the text string anytime there is a space. This way you get "n" columns, each with a different word (A = 820, B = Maple, C = St. D = Zip, etc).

2. Run the find/replace but now you can run an exact match to avoid your problem.

3. Insert a new column in A (I am assuming currently A through column 'n') are the broken pieces of the address

4. In column A use a concatenate formula, I would use the manual version so you get spaces inbetween the pieces:

=B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1

I am assuming that your address probably won't be more than 7 columns, but whatever the max is you need to include that. For addresses less than 7 columns you just harmless spaces after the zip-code (which hopefully are not a problem)

5. Now Column A should have your rebuilt address with the special characters. Copy/Paste special-values only to strip the formulas out.

6. Delete columns B through H (or whatever the max is)

7. Run your text-columms for the special character

That is a good solution, especially to be done in a macro

I might have figured it out on my own, but until it is tried and true I won't know for sure. Instead of doing a find/replace on "ST" I can do a find/replace on " ST", the only thing is that this might mess up the name of the street if it starts with st (or the city, same thing). I will try yours, it sounds more friendly.