Formatting a Person or group type column
This blog demonstrates how you could apply different formatting to a person or group type of column in a SharePoint Online list. With column formatting a last name, first name format is applied.
First Name, Last Name column formatting in SharePoint Online lists. pic.twitter.com/WgLKPI0dh0
— Dennis (@expiscornovus) April 10, 2022
Inspiration
This question from y2kardell:
I have a SharePoint Person Column with a list of names (first name then last name).
I am wanting to use Power Automate to take the name from that column and copy it to a second list, but instead of the first name then last name – I want to have the last name then first name copied the second list. Is this possible?
Power Users Community thread: Take Sharepoint Person Column and Display Last & First Names.
First Name Last Name
Like suggested in the thread we assume a list has been created with a field of type person or group. The names are shown in a First Name Last Name format. In this blog I will share three other formats via SharePoint Online Column formatting.
In this example I am only using one First Name and one Last Name (with one space character). I am aware that people out there might have double names which will result in multiple space characters. This solution approach has not been tested for those type of names..
First Name
Lets start with retrieving the first name of a person. You could use a indexOf operator to find the first space character. You can use that position of that space character in a substring function. The substring returns the part of the string between the start and end indexes.
1. Add a new Single line of text columm to the list. Call it First Name.
2. Select Column Settings, Format this column
3. Add the json code below to the column and click Save.
Last Name
For the Last Name we want to use a substring again. However, in this instance we want to start directly after the space character. This would be the same formula as in the first name + 1.
For the end index we would need to know the length of the whole string. The length function does not support a string value. Microsoft has shared a string length workaround for this by using an indexOf. Just add a unique character (like ^) at the end of your string and find the position of that character.
1. Add a new Single line of text columm to the list. Call it Last Name.
2. Select Column Settings, Format this column
3. Add the json code below to the column and click Save.
Last Name, First Name
You can put the first name and last name together with for example a comma character. Just use the first name + ‘, ‘ + last name syntax.
1. Add a new Single line of text columm to the list. Call it LastNameFirstName.
2. Select Column Settings, Format this column
3. Add the json code below to the column and click Save.
That should be it for the setup.
Happy testing!
Thanks!! This saved my day 🙂
Hi do you know if it is possible to add a column with MS Team channels and format so that when you click on the name of the channel it open up that MS Teams channel.
I am thinking if it could work as you can format a column to open MS outlook (i.e ‘mailto:xxx’).
Hi Christer,
Unless you have the channel Id available in one of your other list columns, I think this would not be possible. A link to a channel normally has this id in the path.
Thank you Dennis, this is a wonderful tutorial. One remark though for Last Name – to avoid getting a comma after the last name you should use the following formula:
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=substring([$Person.title], 0, indexOf([$Person.title], ‘,’))”
}
Hello Dennis, excellent code snippet!
Regrettably, I’ve encountered two limitations:
Users with double names are not handled correctly: their second name appears in the Surname column (although I can work around this).
My users need to extract the List information in Excel, but your JSON formatting doesn’t remain consistent when exported to Excel – the Name and Surname Columns are empty on Excel.
Consequently, I’m experimenting with Power Automate to resolve this issue.
Hi Francesco,
I actually mentioned that double names limitation in my blog as well 🙂
However, just had another look at this. If these are double names in the first name, you could just replace the indexOf by a lastIndexOf function.
Try the below:
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “=substring([$PersonField.title], lastIndexOf([$PersonField.title], ‘ ‘) + 1, lastIndexOf([$PersonField.title] + ‘^’, ‘^’)) + ‘, ‘ + substring([$PersonField.title], 0, lastIndexOf([$PersonField.title], ‘ ‘))”
}
Hello dennis,
The person type (Multi picker) column in sharepoint list automatically resort/regroup the person name and it is not the same sort what I specified in my Input List. For example, I want decision approval hierarchy as,
1. John
2. Peter
3. Harry
After the Input in the list, the person type column gets regrouped and show it as,
1. Peter
2. John
3. Harry
Do you have any JSON formula for this?
Hi Avinash,
It looks like this might be a limitation/by design type of thing of the person or group type of field. In the below Techcommunity thread it is mentioned the order of the field is based on the hidden user information list.
https://techcommunity.microsoft.com/t5/sharepoint/sharepoint-people-group-column-sorting-when-adding-people/m-p/3742155
However, like also mentioned in that thread. If you want to have full control why not use separate fields for each of the approvers?