LinkBack URL
About LinkBacks
I would like to be able to take an address like 18387 S 113Th W Ave and return it as 18387 South 113th West Avenue. I am a slightly familiar with lookup formulas, but I have not had any luck making this work.
I would like to be able to take an address like 18387 S 113Th W Ave and return it as 18387 South 113th West Avenue. I am a slightly familiar with lookup formulas, but I have not had any luck making this work.
Last edited by NBVC; 01-25-2010 at 05:51 PM. Reason: Fix title
You need to supply more examples to see patterns...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
563 South 9Th St
853 W Main St
98 N Battlebridge Dr
35879 Se 48Th St
9863 Morgan Trl
6873 Henslee Dr
891 E 169Th Pl
389 Pioneer St
9868 Sw 63Rd St
1986 Mile Rd Nw
1987 S 74Th West Ave
135 Nesuda Rd
so i want things like Rd returned as Road, S returned as South Ave returned as Avenue and so on and so forth
My only question would be..."why"?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
This code is designed for you to be able to create a table of your own to do the corrections. In column A, list the strings to find. In column B list the replacement values.
Then place your address strings in any other column, select them, and run the macro.
Please Login or Register to view this content.
Last edited by JBeaucaire; 01-25-2010 at 07:23 PM.
I am dealing with thousands of addresses and like them to be in perfect print, makes it easier to read. Thank you for your solution, however I have no idea what to do with itI have not played with macro's. I guess I'll see if I can make sense of it today.
How/Where to install the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The macro is installed and ready to use.
===========
How to use the macro
1) Make sure the strings to match are in column A
2) Make sure the replacement strings are in column B
3) Put the addresses to fix into any other column
4) Highlight the addresses
5) Press Alt-F8 and select AddressCorrections from the macro list.
There is a sample sheet attached to my post above.
thank you, i shall try this this afternoon.
Remember, you'll have to expand the two columns of "search/replace" strings to cover all the things you want.
stcanary
or possibly
Please Login or Register to view this content.
I just came accross this thread this morning. 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?
Thanks
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
It helped alot, Thank You
I am new to the forum. Can you tell me how I can see the attached sample sheet attachment ? I do not see it.
Thanks,
Mike
The only attached sample in this thread is the ReplaceStrings.xls file attached to post #4.
If that is the one to which you are referring, then select the name of the file (may appear in blue text).
Also, your system may not automatically download .xls files so you may have to select again in downloads dropdown (I suppose based on the browser used).
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you. I was able to locate the xls file you referred to.
Mike E.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks