Clay Icon
Join the Community
02 Support

How to Separate Mailing Addresses with Different Formats

Avatar of Joe H.Joe H.
·
·

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

  • Avatar of Channeled
    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.

  • Avatar of Nico M.
    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

    :arc:1
  • Avatar of Channeled
    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.

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·