5 Replies Latest reply on Jan 17, 2015 10:00 AM by Christic

    How Do I remove all text on the left side of a comma and remove comma too.

    Can'twinthelottery

      Title

      How Do I remove all text on the left side of a comma and remove comma too.

      Post

      I have a field titled "Name" that currently has a Last name, First Name format in field. Database was created from an export from an accounting program that had Last and First names combined. I have managed to properly seperate the Last name. However, I have been unable to seperate the first names without having some of the name missing. First names sometimes include Mr. & Mrs. ,John T.,Sarah Sally for example. I want to include everything to the right of the comma but do not want the comma. I have already created a new field called First Name that is currently blank.

        • 1. Re: How Do I remove all text on the left side of a comma and remove comma too.
          philmodjunk

          Your text field shows 2 commas--one just before the first name and one just after the middle initial. Does every single record adhere to that format? In this example, do you want to eliminate all text except "John T." or do you just want "John"?

          You'll need to be careful with any such process as there can be many individual differences in how names are recorded and it's easy to use a solution that works most of the time, but fails with one or two special cases.

          Note: the pattern here suggests that the data was exported as a csv (comma separated value) file. Did you select the CSV file type when importing the data. That option might eliminate the need for much of the special processing to separate out the parts of a person's name.

          • 2. Re: How Do I remove all text on the left side of a comma and remove comma too.
            Can'twinthelottery

            There is only one comma that is before the First name currently. My example of John T. and Sarah Sally and Mr. & Mrs. do not have commas. I was just typing the comma between these as a proper English seperator in my post.  Sorry.. If I could just keep everything to the right of the comma I would be happy.

            • 3. Re: How Do I remove all text on the left side of a comma and remove comma too.
              philmodjunk

              Let ( [ t = YourTextfield ;
                         L = Length ( t ) ;
                         p = position ( t ; "," )
                       ] ;
                       Right ( t ; L - p )
                      )

              • 4. Re: How Do I remove all text on the left side of a comma and remove comma too.
                EssexBiker

                     PhilModJunk

                     This looks like a solution that would get me out of a sticky situation.

                     I have pasted the calculation  but keep getting an error that there are too few parameters in this function.

                     YourTextField has been replaced by the one from my field list

                     The last close bracket on line three becomes highlighted.

                      

                     Any ideas?

                      

                     Regards and ATB

                • 5. Re: How Do I remove all text on the left side of a comma and remove comma too.
                  Christic

                  As you know there are formatting for number fields, however I too have face an issue here I needed the field to be a text field, but if a number is entered, to follow some formatting.  I did a Custom Function that will add a "0" or ".00" if needed and put a comma at the thousand position. If you need more then that you will have to change it a bit..   I'm sure that this is a bit crude for some of you advanced users, so feel free to upgrade it....

                  Hope this will help!!!

                   

                  Function Name:Number_Text

                  Parameters:  Field1

                   

                  If (Filter ( Field1 ; 0123456789 );

                  If (Middle ( Field1; Length ( Field1 )-2 ; 1 )= "." ;  If ( Length ( Field1 )>6 ;  Left ( Field1 ; (Length ( Field1 ))-6 )  & ","  &  Right ( Field1 ; 6 ) ; Field1 )  ; 
                         If ( Middle ( Field1 ; Length ( Field1 )-1 ; 1 )="." ; 

                             If ( Length ( Field1 & "0" ) >6; Left ( Field1 & "0" ; (Length ( Field1 & "0" ))-6 ) & "," &  Right ( Field1& "0" ; 6 ) ; Field1 & "0" )  ;

                             If ( Length ( Field1 & ".00" ) >6; Left ( Field1 & ".00" ; (Length ( Field1 & ".00" ))-6 ) & "," &  Right ( Field1& ".00" ; 6 ) ; Field1 & ".00" ) ) ) ;

                  Proper ( Field1 ))