2 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Need help to split Full Name properly

    Derek _

      I have different file in different format that we receive patient information.  Some times the name is already split up into different columns which makes it easy; but sometimes the file has full name in one column.  I have used LSplit and RSplit with some success.  The problem is when I get capture full name and get list like below.

       

      TRIPLETT W DEWEY

      RECTOR MARY LEE C

      BYRD LOU NELL

      SPEARS LOU ANN W

       

      I am needing LastName FirstName MiddleName fields.

      Been using Lsplit(,3," ",#) # being the appropriate field I need.  But with names like LOU ANN & MARY LEE which are First names this is not working.  ANN & LEE are getting put in the MiddleName field.

       

      How can I get LOU ANN & MARY LEE into the First Name field without messing up other Names in the list?

       

      Thanks

        • Need help to split Full Name properly
          Data Kruncher

          Derek,

           

          Ah, the fun of string manipulations! As is often the case with problems like this, there can be a few different solutions. Here's my approach:

           

          • First, define the whole name as FULL NAME

          • Next, define MIDDLE NAME as:  [font="courier"]rsplit(,10," ",1)[/font][/quote]* Now define FIRST NAME as:  [font="courier"]left(

          lsplit(,2," ",2),

          len(lsplit(,2," ",2))-len())[/font][/quote]* Finally, define LAST NAME as: [font="courier"]left(,instr(' ',[FULL NAME]))[/font][/quote]Based on your layout, that ought to do it.

           

          2nd edit: It occurs to me that a last name containing a space, like "St. James", will be a problem with this approach. I must move on at this point, though. Anyone else have a more complete solution?

           

          Let us know how it works for you.

           

          Regards,

          Kruncher

           

          Edited to show formulas with CODE tags.

          • Need help to split Full Name properly
            Grant Perkins

            Hi Derek,

             

            Names can be so much fun and Kruncher has rightly pointed out one of the problems with name string analysis. Sometimes you can allow for this with a pre-check along the lines of looking for "St ", "St. " or similar - perhaps any space in the first 3 characters of the field, though I do appreciate this may lead to alternative problems with, for example, names of Chinese or Vietnamese origin. You can then make a decision about accepting them as know, valid forms and applying a slightly modified LSPLIT formula or perhaps reporting them as exclusions from automatic processing or as subject for some form of secondary process or maybe even ask for operator input at run time. It would all depend on your needs and purpose for the process.

             

            One thought I has was to do something along the following lines, though this would NOT deal with the issue above where a space exists in the middle of the family name.

             

            Use LSPLIT to create 2 calculated fields. Field one would be intended for LAST NAME and Field 2 THE REST. Then use RSPLIT on THE REST to create 2 fields, the field for MIDDLE NAME at the first space from the right and then WHAT's LEFT. WHAT's LEFT must be the First Name(s). However this will also not work as expected IF the persons MIDDLE NAME should be 2 names!

             

            Just for now I will forget the complication above  - the simplest form of the formulae, other than creating temporary calculated fields for working with, would be very similar to Kruncher's

             

            LAST NAME

            [font="courier"]LSPIT(,2," ",1)[/font][/quote]MIDDLE NAME

            [font="courier"]RSPLIT((LSPIT(,2," ",2)),2, " ",1)[/font][/quote]and FIRST NAME

            [font="courier"]RSPLIT((LSPIT(,2," ",2)),2, " ",2)[/font][/quote]I would be happy to make some suggestions for identifying exception names as discussed but it may be best to know if you have that sort of problem (and some examples!) before diving in to make the whole subject more complicated. Something along the lines of accounting for the data length of the original field already allocated to FIRST NAME and MIDDLE NAME plus any spaces that separate them and deducting that value from the total length of the original field and selecting what is left might satisfy the requirement. Just a slight extension of DK's original suggestion perhaps.

             

            I hope this helps.

             

            Grant