3 Replies Latest reply on Jul 15, 2013 7:23 AM by philmodjunk

    Trim Trailing Carriage Returns

    FionaWilson

      Title

      Trim Trailing Carriage Returns

      Post

           Ok I need to trim some trailing carriage returns like you can do with spaces using TrimAll.

           I have one field that combines a number of fields:

           StatusField - Calculation - PartnershipStatusField  & "¶" & SponsorshipStatusField & "¶"  & DonorStatusField & "¶"  & GrantStatus

           And then I have field that formats a string of text like this:

           StatusFormattingField - Calculation -

           Let([
           L = List ( DevelopmentStatus ) ;
           L = Substitute ( L ; "¶" ; ", " ) ;
           p = Position ( L ; "," ; Length ( L ) ;  - 1 ) ;
           L = Replace ( L ; p ; 1 ; " and" )
           ];
           L
           )

           The result I want is:

           Partner Current, Sponsor Lapsed, Grant Current and Donor Current

           The problem is if it one field is empty then I end up with this:

           Partner Current, Sponsor Lapsed, and

           Instead of this:

           Partner Current and Sponsor Lapsed

           Because my field values have spaces, I can't replace a space with a carriage return because then you end up with this:

           Partner, Current, Sponsor and Lapsed

           Any ideas how I can remove those carriage returns? Or stop it from adding a comma in between the values.

            

           Thanks,

            

           Fiona