Formula field to split text into 3 ways

[Fahad_Sheji]

Hey, I am looking for a formula field to extract city, state and zip code from a full address field

This is how a full address looks like "New York, NY, 10031". I want to split them 3 ways using formula, how can I do that

I used this formula to get the City 

LEFT({Full Address},FIND(",",{Full Address})-1)

[Ron_Daniel]

I'd like to be able to give you a complete answer, but for some reason Airtable doesn't like the regular expression I'm writing for "State". Hopefully someone can add to the conversation to help us figure that part out.

The formula for "City" is

REGEX_EXTRACT({Full Address Line}, "^[^,]*")

The formula for "State" (which is throwing an error) is

REGEX_REPLACE({Full Address Line},"(?<=,\\s)(.*)(?=,\\s)")

The formula for "Zip" is

REGEX_REPLACE({Full Address Line},"\\w+.*\\, ","")

[Steve_Haysom]

Hi again Ron!

After some searching I found the exact rules that airtable regex supports here, in a link from here.

So it looks like they don't support assertions such as look ahead and behind (?<= and ?=):

(?=re)	before text matching re (NOT SUPPORTED)
(?!re)	before text not matching re (NOT SUPPORTED)
(?<=re)	after text matching re (NOT SUPPORTED)

for State I tried:

TRIM(REGEX_REPLACE(REGEX_EXTRACT({Full address line}, "\\,[ A-Z]+"), ",", ""))

as an alternative for Zip, you could do a mirror image of the City regex like so: 

REGEX_EXTRACT({Full address line}, "[^, ]+$")
- tie to end instead of beginning of the text using $, ie match from
end backwards till just before first comma or space (in fact I don't think you need the comma in there
perhaps just the space would do)