Help

Formula field to split text into 3 ways

Topic Labels: Formulas
1694 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Fahad_Sheji
6 - Interface Innovator
6 - Interface Innovator

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

Fahad_Sheji_1-1674835300739.png


I used this formula to get the City 

 

 

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

 

 

 

2 Replies 2
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

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+.*\\, ","")

Screen Shot 2023-01-27 at 12.49.12 PM.png

Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

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)