4 Replies Latest reply on Feb 7, 2009 11:17 PM by ralvy

    Separating data

    gary1987

      Title

      Separating data

      Post

      Thanks for solving my last question it worked well. Challenge is I have addresses in one database but the addresses all came in 3 or so different styles. All I want is the first and last, is there a functio I can write that will achieve this in one pass. The styles are all under one "Fullname" field but are as follows. I don't need middle name or Jnr etc.

       

      Hopkins Gary

      Hopkins Gary J

      Hopkins Gary Jnr

       

      These are the most common 

        • 1. Re: Separating data
          Sorbsbuster
            

          Oh, well, here's my second go at using an oldie-but-goodie method:

           

           

          Try:

           

          LastName =

           

          Left (

           

          FullName ; ( (Position (FullName) ; " " ; 1 ; 1 ) - 1 )

           

          )

           

          In other words: extract the characters starting from the left, until it meets the first space.

          -------------------------

           

          FirstName =

           

          Right (

           

          Fullname ;

           

          ( (Middle (FullName) ;

           

          (Position (FullName) ; " " ; 1 ; 1 ) + 1 ;

           

          (Position (FullName) ; " " ; 1 ; 2 ) - (Position (FullName) ; " " ; 1 ; 1 )

           

          )

           

          IOW: Extract all the letters in the middle of FullName, starting from the first space + 1, and counting for the number of characters betwen the first space and the second space.

          --------------------------------------------------------------------------

           

          (Sorry - may have got the odd +/-1 wrong there - you get the principle...)

           

          HTH,

          Alan.

          • 2. Re: Separating data
            ralvy
              

            This might work too:

             

            Last Name =

            LeftWords ( FullName ; 1 )

             

            First Name =

            RightWords ( LeftWords ( FullName ; 2 ) ; 1 )

             

            The first one is obvious. The second one takes the first two words (FullName;2), and then takes the first word it finds in (FullName;2), counting from the right.

            • 3. Re: Separating data
              raybaudi
                

              ralvy wrote:

               

              First Name =

              RightWords ( LeftWords ( FullName ; 2 ) ; 1 )

               


              Forgot MiddleWords ( ) ?

               

              First Name =

              MiddleWords ( FullName ; 2 ; 1 )


              • 4. Re: Separating data
                ralvy
                   Ah. Didn't notice MiddleWords. Still new to FMP.