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

# Calculation to insert comma and "and" in list

### 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

• ###### 1. Re: Calculation to insert comma and "and" in list

Try:

`Let([L = List ( <fieldname> ) ;L = Substitute ( L ; ¶ ; ", " ) ;p = Position ( L ; "," ; Length ( L ) ;  - 1 ) ;L = Replace ( L ; p ; 1 ; " and" )];L)`
• ###### 2. Re: Calculation to insert comma and "and" in list

This almost works perfectly! Doesn't work so great if there is only one name, however, that was easily fixed with a simple IF statment:

If ( Count ( fieldname ) = 1 ; fieldname ; Let([
L = List ( Mship_Con_ByID_All::c_FullName ) ;
L = Substitute ( L ; ¶ ; ", " ) ;
p = Position ( L ; "," ; Length ( L ) ;  - 1 ) ;
L = Replace ( L ; p ; 1 ; " and" )
];
L
))

Thanks so much for your help!

Fiona