Extracting the last two characters of a string IF certain conditions are met

[PocketListings_]

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.

Thanks, Airtable!

[PocketListings_ · Accepted solution]

Found a formula that works!

IF(FIND(“St”, RIGHT({Property Address}, 2)),LEFT({Property Address},LEN({Property Address})-2))