St. and Saint

[CW-NEN]

I have a whole list of cities. Some people use abbreviations (St, MT and FT), others use abbreviations with punctuation (St., MT. and FT.) and still others type out the full word. I was hoping there is a way of normalizing all of these entries, without doing it by hand.

Thanks
Chris

 

[Damon Ostrander]

Hi Chris,

Here is a simple macro that should help:

Sub Standardize()
   'Standardizes abbreviations St, Ft, and Mt to St., Ft., and Mt.
   Dim Cell    As Range
   For Each Cell In Selection
      Cell = Replace(Cell.Text, "Mount ", "Mt. ")
      Cell = Replace(Cell.Text, "Saint ", "St. ")
      Cell = Replace(Cell.Text, "Fort ", "Ft. ")
      Cell = Replace(Cell.Text, "Mt ", "Mt. ")
      Cell = Replace(Cell.Text, "St ", "St. ")
      Cell = Replace(Cell.Text, "Ft ", "Ft. ")
   Next Cell
End Sub

This macro should be placed in a standard macro module. To do this, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane. To use this macro, select the entire range of cells you would like it to process, then run the macro (Alt-TMM).

I should mention that I assumed that if you have an address, such as

345 Mt Vernon St, Roanoke VA 12345

that the St (street abbreviation) would always be followed by a comma as in this example so that it would always be distinguishable from a saint abbreviation. If this is not true, the problem gets a bit more complicated.

I hope you find this helpful.

Keep Excelling.

Damon

 

[CW-NEN]

Works great.

Thanks