Jack Deuce wrote...
....
501 East Station St *as entered should convert to

501 E. Station St. * * *East to E. & Street to St.

The problem is when checking for St and replaceing with St. will also
change Station to St.ation
....

The Range class's Replace method is an overly blunt tool. You'd have
much finer control reading cell contents into a VBA string variable,
making the changes to that string variable, then writing the modified
string back to the cell's value.

However, you have bigger potential headaches ahead. How would you
distinguish St meaning Street from St meaning Saint? E.g.,

123 St James St

If you had any Washington, DC addresses, you'd need to accommodate
addresses like

111 Massachusetts Ave NW

but there could be St rather than Ave, so there should be St not at
the end which should become St. and other instances which should.
Further complicating things, how about distinguishing Ave above with
Ave Maria Boulevard (there are a few in the US)? Or some of my
favorites from around where I live: Street Road, Boulevard Way, Circle
Court, Tyson Circle.

If your goal is eliminating text strings which differ but refer to the
same address, then uniformity is the goal rather than brevity. Fully
spelling out every word in the database would work best. If you want
to save on ink when printing addresses, use a specific purpose filter
at that stage to convert East to E., Street to St., etc. just before
printing (though the USPS would be perfectly fine with no periods, so
you could save even more ink dispensing when them). It's much easier
to abbreviate text than expand it.

Back to the question of how to transform text. Add spaces at the
beginning and end of each address to make it easier to check entire
words, then search for " St " or " East " rather than "St" or "East".