Just guessing here
Private Sub ExpandAbbreviation()
Dim lr as Long
Dim dRng as Range
lr = Cells(Rows.Count,"E").End(xlUp).Row
Set dRng = Range("E2:E" & lr)
For each cell in dRng
Call pvtReplace("rd.","Road")
Call pvtReplace("st.","Street")
Call pvtReplace("ln.", "Lane")
Call pvtReplace("Blvd", "Boulevard")
Call pvtReplace("E.", "East")
Call pvtReplace("N.", "North")
Call pvtReplace("W.", "West")
Call pvtReplace("S.", "South")
Next
End Sub
Private Sub pvtReplace(Abbr as String, Real as String)
Selection.Replace What:=Abbr, Replacement := Repl, Lookout := xlPart, SearchOrder := xlByRows, _
MatchCase := False, SearchFormat := False, ReplaceFormat := False, FormulaVersion := xlReplaceFormula2
End Sub
As both Paul and June7 have alluded to, this method will fail based on a number of issues. For example "NW" will not find "North West" because you haven't yet defined it. What happens if the data entry was simply "S" or "N" where the punctuation point wasn't entered? If time was on your side perhaps you could keep adding Call pvtReplace to each and every abbreviation but this could be nearly endless. It will also fail when you have abbreviations St. John for example.