Thread: Replacing Abbreviations in Street Addresses

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.

You need to supply more examples to see patterns...

My only question would be..."why"?

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.

Option Explicit

Sub AddressCorrections()
'JBeaucaire  1/25/2010
'Replace text strings in address values
Dim MyArr, Vals, i As Long, v As Long, r As Long, buf As String

If MsgBox("Have you selected the cells to perform the corrections?", vbYesNo + vbQuestion) = vbNo Then Exit Sub

On Error Resume Next

MyArr = Application.WorksheetFunction.Transpose(Selection.Value)

For i = 1 To UBound(MyArr)
    Vals = Split(MyArr(i), " ")
    For v = 0 To UBound(Vals)
        r = Application.WorksheetFunction.Match(Vals(v), Range("A:A"), 0)
        If r <> 0 Then Vals(v) = Range("B" & r)
        r = 0
        buf = buf & " " & Vals(v)
    Next v
    MyArr(i) = Trim(buf)
    buf = ""
Next i

Selection.Value = Application.WorksheetFunction.Transpose(MyArr)
End Sub

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.

Remember, you'll have to expand the two columns of "search/replace" strings to cover all the things you want.

stcanary
or possibly

Sub ptest()
With Sheets("Sheet1").Columns("A")        ' change to columns to replace string
    .Replace What:=" N ", Replacement:=" North ", SearchOrder:=xlByColumns, MatchCase:=True
    .Replace What:=" S ", Replacement:=" South ", SearchOrder:=xlByColumns, MatchCase:=True
   .Replace What:=" E  ", Replacement:=" East ", SearchOrder:=xlByColumns, MatchCase:=True
   .Replace What:=" W ", Replacement:=" West ", SearchOrder:=xlByColumns, MatchCase:=True
 End With
End Sub

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.