St. and Saint

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Chris,

Here is a simple macro that should help:

Code:
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top