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

    Calculation to insert comma and "and" in list



      Calculation to insert comma and "and" in list



      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.