none
how to change the format of the “person or group” column RRS feed

  • Question

  • In my document set I have a column with type of “person or group” which I can add the name of some users to it.

    The format of the names is like this: firsName1 Surname1; firstNmae2 Surname2;

    However I need to change the name format in this way: Surname1, firstName and Surname2, firstNmae2

    Is it possible to use calculated column to convert this column to this format? Or maybe by the use of workflows!

    what are the possible solutions?

    Monday, July 29, 2013 5:49 PM

Answers

  • Hi pax123,

    You cannot use calculated column with Person or Group fields. Only formula's listed here can be used: http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx

    You will have to use combination of things here. First use a simple SPD workflow to copy the Person or Group field to another column (say NewName) of Single Line Text format. Simple action in workflow will be "set field in Current Item". Select Return Field as Display Name.

    Then create a calculated column FirstName: =LEFT(NewName,SEARCH(" ",NewName)-1)

    Another column LastName= RIGHT(NewName,SEARCH(" ",NewName))

    then create another calculated column: FinalName=LastName&", "&FirstName

    Or use them in one field itself:

    =RIGHT(NewName,LEN(NewName)-SEARCH(" ",NewName))&", "&LEFT(NewName,SEARCH(" ",NewName)-1)

    See this: http://social.technet.microsoft.com/Forums/sharepoint/en-US/b9fad287-7174-4a6b-b21b-7eacb661aef7/how-to-retrieve-substring-of-another-field-using-calculated-field

    These all calculations can be only used in single selection of Person and not under multiple selection. Also what is the user has a middle name as well? You might also see an issue that FirstName and LastName calculations are empty when the item is created. This might happen because the NewName field has not been populated till the workflow completes after item creation. Test and see how it works. Hope you find a better resolution.


    Regards, Kapil ***Please mark answer as Helpful or Answered after consideration***



    • Edited by K-Joshi Monday, July 29, 2013 8:02 PM edited
    • Marked as answer by star.warsModerator Thursday, August 8, 2013 1:19 PM
    Monday, July 29, 2013 6:15 PM

All replies

  • Hi pax123,

    You cannot use calculated column with Person or Group fields. Only formula's listed here can be used: http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx

    You will have to use combination of things here. First use a simple SPD workflow to copy the Person or Group field to another column (say NewName) of Single Line Text format. Simple action in workflow will be "set field in Current Item". Select Return Field as Display Name.

    Then create a calculated column FirstName: =LEFT(NewName,SEARCH(" ",NewName)-1)

    Another column LastName= RIGHT(NewName,SEARCH(" ",NewName))

    then create another calculated column: FinalName=LastName&", "&FirstName

    Or use them in one field itself:

    =RIGHT(NewName,LEN(NewName)-SEARCH(" ",NewName))&", "&LEFT(NewName,SEARCH(" ",NewName)-1)

    See this: http://social.technet.microsoft.com/Forums/sharepoint/en-US/b9fad287-7174-4a6b-b21b-7eacb661aef7/how-to-retrieve-substring-of-another-field-using-calculated-field

    These all calculations can be only used in single selection of Person and not under multiple selection. Also what is the user has a middle name as well? You might also see an issue that FirstName and LastName calculations are empty when the item is created. This might happen because the NewName field has not been populated till the workflow completes after item creation. Test and see how it works. Hope you find a better resolution.


    Regards, Kapil ***Please mark answer as Helpful or Answered after consideration***



    • Edited by K-Joshi Monday, July 29, 2013 8:02 PM edited
    • Marked as answer by star.warsModerator Thursday, August 8, 2013 1:19 PM
    Monday, July 29, 2013 6:15 PM
  • Is it possible to do this on a multiple people picker field? How would you reference each one? thank you.

    David Narramore

    Friday, May 17, 2019 1:59 PM