One needs a 2 instead of a 3, in order to avoid a leading space in the
output. And this leaves the Active Cell with the state designation still
in it.

If the city names were all one word, then Data|Text to Columns would
suffice. Otherwise some code has to be written to strip out the State
designation and the space preceding it. If noone answers soon, I'll
work on it.

Alan Beban

Ron de Bruin wrote:
Sub test()
ActiveCell.Offset(0, 1).Value = Right(ActiveCell.Value, 3)
End Sub

Try this