3 Replies Latest reply on May 22, 2011 11:29 PM by LaRetta_1

    Remove leding zeros from a list of addresses

    BretMiller

      Title

      Remove leding zeros from a list of addresses

      Post

      I have a field that contains addresses, when the records were generated it was done so that they could accomadate up to a 6 digit street number.  So you will see addresses that look like 001234 Main St.  What can I do to remove the leading zeros?

        • 1. Re: Remove leding zeros from a list of addresses
          LaRetta_1

          Create this as a calculation (result is text) and compare it through all of your records before implementing it.  And back up first.  I worry that some addresses might not fit the pattern I have attempted to protect from, since you only provided one example of the data.  Replace 'yourFieldName' with your address field name.

          Let ( [
          t = yourFieldname ;
          x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

          l = Length ( LeftWords ( t ; 1 ) ) ;
          n = GetAsNumber ( LeftWords ( t ; 1 ) )
          ] ;
          Case (
          x ; n & Right ( t ; Length ( t ) - l )  ; t
          ) // end case
          ) // end let


          1 of 1 people found this helpful
          • 2. Re: Remove leding zeros from a list of addresses
            BretMiller

            LaRetta,

            thank you for your help.  I probably should have provided more examples of the data set.  other examples would include "000056 Side St" which needs to be "56 Side St" and "003004 Chestnut Way" which needs to be "3004 Chestnet Way" or even "000000 Hulen Road" which needs to be "Hulen Road".  Will the function you provided work in these other instances as well?

            • 3. Re: Remove leding zeros from a list of addresses
              LaRetta_1

              It would have broken for "000000 Hulen Road" so I have modified it for you.  It will work in the other examples.

              Trim (
              Let ( [
              t = yourFieldName ;
              x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;
              l = Length ( LeftWords ( t ; 1 ) ) ;
              n = GetAsNumber ( LeftWords ( t ; 1 ) )
              ] ;
              Case (
              x  ; Case ( n > 0 ; n ; "" ) & Right ( t ; Length ( t ) - l )  ; t
              ) // end case
              ) // end let
              ) // end trim