+4Hey, 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)
+21I'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
+11I'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
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)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.