I’m probably overthinking this, but struggling to find the right formula to accomplish the following:
I have a list of addresses in abbreviated format “123 Main St”, but some addresses have numbers on the end (“123 Main St #2A”).
I’m trying to find a way to extract the “St” without affecting addresses with street names like “State” or “Sterling”.
It doesn’t need to be perfect, so addresses with St that have a number after them can remain in that format. I’m thinking I need a formula that combines IF, RIGHT and SUBSTITUTE, but can’t quite get it. IF “ST” is contained in the last two characters, extract them.