cancel
Showing results for 
Search instead for 
Did you mean: 

How-To: Properly punctuating a list of items

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.

C.jpg


We build the properly punctuated list as follows:

List Logic.png
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.)

Final List Logic.png

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!

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim