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

    Separating data



      Separating data


      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



        • 1. Re: Separating data

          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



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


          And for the last name



          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'.)





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


          • 2. Re: Separating data

            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

              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.



              • 4. Re: Separating data

                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

                  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

                    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

                      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

                        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.