8 Replies Latest reply on May 4, 2009 10:25 PM by ziggy

    Separating data

    gary1987

      Title

      Separating data

      Post

      I am a new filemaker user and have  a database which includes the first and last name in the same field , can I separate these out into 2 fields  , first and last.

       

      Also  can I remove the last 4 numbers on a field that has zip + 4 when I only need a 5 digit zip

       

      Thanks

        • 1. Re: Separating data
          Sorbsbuster
            

          Hi, gary1987, and welcome to the Filemaker forum!

           

          All you need to achieve the first is a consistent delimiter between the first name and the last name.  If we can assume that they are always separated by a <space>, then what you want are the calculations:

           

           

          For the first name

           

          Left

          (
          NameFull ;
          Position ( NameFull; " "; 1; 1) - 1
          )

           

          And for the last name

           

          Right

          (
          NameFull ;
          Length (NameFull ) - Position ( NameFull; " "; 1; 1)
          )

           

          If you want an explanation of what they are doing, just ask.

           

          (Of course, if the names are not consistently in the format: 'FirstName <space> LastName' then you will have more fun.   Like, with: 'Sarah Michelle Geller', or 'P J O'Rourke'.)

           

          HTH,

          Alan.

           

          (If you can see the logic of the FirstName calculation, then the zipcode should be similar.)

           



          • 2. Re: Separating data
            ralvy
              

            Still new to FM. That said, why wouldn't you just use this:

             

            First Name:

            LeftWords ( NameFull ; 1 )

             

            Last Name:

            RightWords ( NameFull ; 1 )

             

            Of course this assumes NameFull is always composed of two words.

            • 3. Re: Separating data
              Sorbsbuster
                

              Why wouldn't you? Because I learned that way 15 years ago, always works for me, ain't broke, why fix it, and I really must get round to reading up on all these new-fangled functions the FM bods keep adding.

               

              ralvy is dead right, of course.

               

              Alan.

              • 4. Re: Separating data
                ziggy
                  

                I've got a database with exactly that challenge - a name field in which records show variously as "Fred Bloggs" and "Dr Fred Bloggs" (also variations such as Mr, Ms, and Mrs).

                I need to get them into separate honorific, firstname, and lastname fields.

                RightWords seems to do the lastname consistently.

                But I'm stumped with the variations for the rest of it.

                Grateful for your help.

                • 5. Re: Separating data
                  ralvy
                    

                  Well, assuming you don't have any Middle Names, what about this:

                   

                  Honorifc field: RightWords (NameField ; 3 )

                  FirstName field:  RightWords (NameField ; 2 )

                  LastName field: RightWords (NameField ; 1 )

                  • 6. Re: Separating data
                    ziggy
                      

                    Unfortunately, that doesn't work - that honorific field returns the full content of the Name field (e.g. Dr Fred Bloggs), and the firstname field returns Fred Bloggs.

                     

                    Part of the problem is that the data is not consistent - some records have just Fred Bloggs.

                     

                    It needs to be able to cope with the variations.

                    • 7. Re: Separating data
                      ralvy
                        

                      Ah, yes. My error. Try this instead:

                       

                      Honorifc field:

                      If ( WordCount ( NameField ) = 3 ; LeftWords ( NameField ; 1 ) ; "" )

                       

                      FirstName field:

                      LeftWords ( RightWords ( NameField ; 2 ) ; 1 )

                       

                      LastName field:

                      RightWords ( NameField ; 1 )

                      • 8. Re: Separating data
                        ziggy
                          

                        Aha - magic!

                         

                        I was thinking about the IF function but couldn't figure how to use it with XXXWords. Nesting RightWords within LeftWords is also an elegant solution.

                         

                        Many thanks.

                         :smileyhappy:

                        Z