3 Replies Latest reply on Nov 17, 2008 7:49 AM by MS2

    Removing the last word from a line of text

    MS2

      Title

      Removing the last word from a line of text

      Post

      I am trying to separate a 'full name' field into just two fields 'last name' and 'first name(s)'.

       

      I have successfully extracted the last name by using the RightWords text function and placed this into a new field.

       

      Now I want to either remove the last name from the original field of extract all the text that came before the last name to place into another new field called 'first names'. I cannot use the LeftWords function as the number of words will vary from record to record.

       

      I know there will be a simple solution but I only seem to find a way to trim letters not words.

       

      Could anyone point me in the right direction please? 

       

       

        • 1. Re: Removing the last word from a line of text
          Dirk
            

          You  could use the Length function to determine a) the number of characters in the field with the full name, and b) determine the number of characters in the field with the last name.

           

          Substract the difference between the two from the full name and use it in the Left function to pick up the remaining characters.

           

          But.

           

          It's easy when someone is called John Jones. Using this method, John Paul Jones also gets extracted correctly.

           

          But what do you do when someone is called Salvatore di Luca, for example? Gerhard von der Heide? John Paul Jones Smith? Consuela Garcia Perez?

           

          In a situation like this, I'd be tempted to have a first name field that simply takes the first word from the full name as a first name, the last word as the last name and leave the rest, literally, in a field between the two, to be corrected by the person who gets to handle that persons record.

          • 2. Re: Removing the last word from a line of text
            raybaudi
              

            Hi MS2

             

            "I have successfully extracted the last name "


            If what said is really true, how about:

             

            first name(s) = Trim ( Substitute ( full name ; last name ; "" ) )

             

            or, even better:

             

            LeftWords ( full name ; WordsCount ( full name ) - 1 )

            • 3. Re: Removing the last word from a line of text
              MS2
                

              Hi Daniele,

              That seems to work well, thank you.

               

              Steven