How to Separate Mailing Addresses with Different Formats

Right now I have a list of addresses and i need to separate the mailing address, city, and state, and zip code. It's mostly US addresses but a few international. Some of the entries have the state name capitalized (Pennsylvania) others have it lower case (pa) and others have it uppercase (PA). any ideas?

4 comments

Channeled
APP
·
·
To separate mailing addresses into components, we recommend using our "Validate U.S. Address with Lob" or "Validate International Address with Lob" actions, depending on the address type. These actions format and validate addresses, providing separate fields for components like city, state, and zip code. For handling variations in state formatting, you can use our "Normalize Location with Mapbox" action. This will standardize location data, including state names. After processing, you can use our "write to table" feature to store the separated and standardized address components in your Clay table.
Sources: Normalize Location with Mapbox | Clay.com Validate International Address with Lob | Clay.com Validate U.S. Address with Lob | Clay.com
Nico M.
·
·
Joe H. I’ve done this with multiple formulas pretty accurately. extract street address formula: {{address}}?.split(",")?.[0] extract city: {{address}}?.split(",")?.[1]?.trim() extract state: {{address}}?.match(/,\s*([a-z]{2})\s*\d{5}/i)?.[1]?.toUpperCase() extract zipcode: {{address}}?.match(/\b\d{5}\b/)?.[0] ai prompt is: remove everything but the zip from /address column example: 42 janis st, new orleans, la 70118 = 70118
1
Channeled
APP
·
·
To separate mailing addresses into components, we recommend using our "Validate U.S. Address with Lob" action for U.S. addresses and "Validate International Address with Lob" for non-U.S. addresses. These actions format and validate addresses, providing separate fields for components like street address, city, state, and zip code. For handling variations in state formatting, you can use our "Normalize Location with Mapbox" action. This will standardize location data, including state names. After processing, you can use our "write to table" feature to store the separated and standardized address components in your Clay table. This approach should work for both U.S. and international addresses, handling variations in formatting and providing standardized results.
Sources: Normalize Location with Mapbox | Clay.com Validate International Address with Lob | Clay.com Validate U.S. Address with Lob | Clay.com
Channeled
APP
·
·

How to Separate Mailing Addresses with Different Formats | Clay