2 Replies Latest reply on Jun 28, 2012 10:44 PM by FionaWilson

    Calculation to insert comma and "and" in list

    FionaWilson

      Title

      Calculation to insert comma and "and" in list

      Post

      Hi,

      I am trying to format a list of three name so that it goes from this:

       

      Fiona Willson

      Pablo Picasso

      Andy Warhol

       

      To this:

      Fiona Wilson, Pablo Picasso and Andy Warhol

       

      I've worked out how to insert commas OR an "and" into a list using the substitute function which is great:

      Substitute ( List ( <fieldname> ) ; ¶  ; ", " )

      But I can't seem to work out how to insert a comma between the first two names and an "and" between the last two names. I know the answer must be with the Left/Right/MiddleValue functions but I can't seem to get it to work.


      Thanks,

      Fiona