1 Reply Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    Calculated Field Help

    pistol654 _

      Hello,

       

      I am attempting to try something which I am afraid is simply not possible.  I have a column that contains lots of data, much of which is the same with only slight differences.  An example is as follows:

       

      John D #12

      John L #5

      Mark #4

      John A #8

      Bill T

      Paul F #98

      John G

       

      etc...

       

      What I want is for every cell that contains "John" in any form to generate in a calculated field as "John".  I know I could do this with the vlookup calculated field, but unfortunately I do not know how many individual names I have nor do I know how many different iterations of each name I have either.

       

      I have successfully used the Strip() function to remove numbers and symbols, but I can't get past that.  I was wondering if there is a formula that would state that if an common strings of a certain length will default to the first occurrance (i.e. "John D", "John L", "John A", and "John G" all will appear as "John D").

       

      Keep in my that my current file as over 124,000 rows, hence why I am hoping for a way to simplify this process. Any advice or thoughts would be much appreciated! I hope my explanation makes sense.  

       

      Thanks!

       

      Pistol

        • Calculated Field Help
          Data Kruncher

          But I'm a little fuzzy as to what you really need here.

           

          To isolate the first name in the field, use FirstName as:

           

          LSplit(NameField,2," ",1)[/CODE]

           

          To find the first occurrence of each name, you could build a field with RecNo(), and then sort on FirstName and that RecNo field.

           

          To do fancier things, multiple passes with different models would likely be required.

           

          Does that help to get you going?