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?
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.
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
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.