AnsweredAssumed Answered

Calculation to insert comma and "and" in list

Question asked by FionaWilson on Mar 29, 2012
Latest reply on Jun 28, 2012 by FionaWilson


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.