How-To: Properly punctuating a list of items

[LRVinNC]

With a bit of work you can build properly punctuated lists by utilizing a combination of Concatenate, Concat, Split, CountRows, FirstN, and Last.  Whew! 

Others may have a more elegant way of accomplishing this, but this one definitely works for me.  I actually whittled this version down as I was preparing this blog from the much clunkier version I started with. Continuous improvement!  It's the name of the game!

So, for this example, I used a Collection (MyPeople) with one column (Name).  I gave it 5 names to work with, but the logic is independent of list size.

We build the properly punctuated list as follows:

Let's break this down to make it easier to follow:

• Concat(MyPeople,Name,"; ")
  Concatenate the items in the collection (or list) along with a semicolon creating a list like:
  Name1;Name2;Name3;Name4;Name5;
  Note that we will need to go back and eliminate the semicolon after Name5 and replace the one before it with our & (or the list separator of your choice).
  
• Split(Concat(MyPeople,Name,"; "),";")
  Now go back and split this list again on the semicolon.  This likely seems counterintuitive but this will put the list of names into a simple result table we can feed to the next step. When I try omitting this step, I wind up with a pile of semicolons and nothing else.  
  Note:  I recommend using semicolons, not commas here, especially if you are working with DisplayName which contains a comma.  I just stick with semicolon so I have consistency.
• FirstN(Split(Concat(MyPeople,Name,"; "),";").Result, CountRows(MyPeople)-1)
  Count the number of rows in the list, MyPeople, and subtract one.  This is the number oft items you want to have before the ampersand.  Then use FirstN to reduce the list to table size - 1 items
• Concat(FirstN(Split(Concat(MyPeople,Name,"; "),";").Result, CountRows(MyPeople)-1), Result & ",")
  Add the comma (or other desired punctuation you want in your final results.  
  

We are almost there!  All that is left is to tack on the final punctuation and final name.

• Concatenate(Concat(FirstN(Split(Concat(MyPeople,Name,"; "),";").Result, CountRows(MyPeople)-1), Result & ","), " & ", Last(MyPeople).Name
  For our final step, concatenate the string containing the complete punctuated list of items up through just before the final name we prepared above, plus the final punctuation we want - in this case an ampersand -  and the last item in the list.  
  Note:  We could have used " and ", " or ", " / " or any other final separator our heart desires.

Applying this logic to the 5-item collection above yields the desired result:
John Doe, Jane Snow, Abby Normal, Slim N None & Jack Spratt

One final tweak is needed to handle scenarios with only one or two names correctly by wrapping our logic in an If statement.  We'll throw in a check for an empty collection, just for good measure.  (Thanks @CBroeker for asking about how to handle 1 and 2 name cases.)

In addition to using the collection demonstrated above, I have tested the logic with a Sharepoint list using both a Person column and a single line text column.  Both works identically.

I hope this helps you build cleaner lists for use in labels or HTML Text output.  If you have a leaner method, please share!