5 Replies Latest reply on Jan 20, 2010 11:22 AM by mrvodka

    House Numbers in Address Field

    d.justins

      Title

      House Numbers in Address Field

      Post

      Hi all,

       

      I have alot of data that was imported into the database i am working with and someone didn't set it up right initiallly, so now i have the street numbers in front of the street name in the same field. They are separated with a space. I'm sure it's simple, but i need some direction. I was wondering if it is possible to separate the house number from the street name using a script. I can figure out the loop and everything, i'm just not certain of the functions needed to do this. 

       

      Thanks

       

      - justin

        • 1. Re: House Numbers in Address Field
          mrvodka
             Look into the LeftWords, MiddleWords, and RightWords functions. Play with them to parse out the info. If you still have trouble, come back and post what specificlly. Also you can use Replace Field Contents instead of a loop to massively replace a field in the found set.
          • 2. Re: House Numbers in Address Field
            d.justins
               How would i delete the 1st leftword?
            • 3. Re: House Numbers in Address Field
              Steve Wright
                

              You could use something simple like the following 

               

              Define 2 new fields, then set their calculations as below 

               

              For StreetField :

              filter ( AddressLine1 ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- " )

               

              For HouseNoField :

              filter (AddressLine1 ; "0123456789" )

               

              However, what about the instances where the house number is actually a house name...

              perhaps then you need to start looking how they seperate the data

               

              Do they use comma's  such as

              Willow House, The road.

              9, The Road

               

              If so, you can then look for the separator character and use the Left / Right functions 

              • 4. Re: House Numbers in Address Field
                mrvodka
                  

                d.justins wrote:
                How would i delete the 1st leftword?

                Like I said, try using MiddleWords, or RightWords ( Hint you can count the number of words using WordCount ) and experiment.


                • 5. Re: House Numbers in Address Field
                  mrvodka
                    

                  SW wrote:

                  You could use something simple like the following 

                   

                  Define 2 new fields, then set their calculations as below 

                   

                  For StreetField :

                  filter ( AddressLine1 ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- " )

                   

                  For HouseNoField :

                  filter (AddressLine1 ; "0123456789" )



                   

                  This wont work if the address is like:

                   

                  111 Avery Place Apt 42