Add an "and" to a list

[rick altman]

I have a field that receives a bunch of first names from a portal, thanks to the List function. The result might look like this:

 

Alice, Bill, Chuck

 

However, I need for the list to read:

 

Alice, Bill, and Chuck

 

 

I need to find the last comma in the text and add an "and" to it. I have been messing around with the Position function stuffed inside of a Substitute function, but I can't get it right. Can someone help?

 

FMPA14

 

 

Rick A.
Pleasanton CA

[LaRetta]

Hi Rick,

It is best not to create another calculation which adjusts your first calculation because cascading calculations are very slow.  Instead, I suggest that you replace yours with something like this:

Let ( [
values = List ( yourRelatedNames ) ;
pos = Position (  values ; ¶ ; Length ( values ) ; - 1 ) ;
fix = If ( pos ; Replace ( values ; pos ; 1 ; " and " ) ; values )
] ;
Substitute ( fix ; ¶ ; ", " )
)

It is simpler to handle the fix before the substitute. :-)

Edited February 5, 2016 by LaRetta