9 Replies Latest reply on Oct 2, 2018 2:55 PM by Shinichiro Murakami

    Location splitting

    Bhumika Bhandari

      Hello Everyone,

       

      I have records which contain Location: which contains data like:

      Location column:

       

      Hollywood, FL, USA
      Pembroke Park, FL 33021, USA
      Franklin Lakes, NJ 07417, USA
      Chicago, IL 60607, USA; Boston, MA, USA; Maine, USA; New England, USA
      Alpharetta, GA, USA
      Pennsylvania, USA
      St. Louis, MO, USA
      Charlotte, NC, USA; Rock Hill, SC, USA
      St. Louis, MO, USA
      St. Louis, MO, USA
      St. Louis, MO, USA
      Naperville, IL, USA; Chicago, IL, USA; Elmwood Park, IL 60707, USA; Aurora, IL, USA; Bolingbrook, IL, USA; Montgomery, IL, USA; Oswego, IL, USA; Plainfield, IL, USA
      St. Louis, MO, USA
      St. Louis, MO, USA
      St. Louis, MO, USA
      St. Louis, MO, USA
      San Francisco, CA 94105, USA
      MARTINS ADD, MD 20815, USA
      St. Louis, MO 63141, USA
      20 herbert place, jersey city, nj 07306
      Toronto, ON, Canada
      Peoria, IL 61614, USA; Washington, IL 61571, USA; Dunlap, IL 61525, USA; Morton, IL 61550, USA; Chillicothe, IL 61523, USA; Metamora, IL 61548, USA; Bloomington, IL, USA; Normal, IL, USA
      Florence, KY, USA; Ohio, USA; Indiana, USA
      Chicago, IL 60647, USA
      Miami, FL, USA
      64541 30th St, Lawton, MI 49065, USA
      Tyler, TX, USA; Texarkana, TX, USA; Longview, TX, USA; Marshall, TX, USA; New Boston, TX 75570, USA; Atlanta, TX, USA; Queen City, TX 75572, USA; Linden, TX 75563, USA; Mt Pleasant, TX 75455, USA
      Florida, USA

       

      I want to create this Location split it into Address, City, State, Country, and Zip code. But each and every record has a different type of data into the location field and the zip code sequence is also not consistent. So, I can't use Custom split using comma because it will not give me correct answer.

       

      So, how can I  create a split so that I can get Address, city, state, country and zip code automatically?

       

       

      Let me know if you need me to clarify something regarding this issue.

       

       

       

      Thanks,

      Bhumika