none
Hide extra characers from people picker field to export in SharePoint RRS feed

  • Question

  • Hello,

    I have a people picker field that gets the current user's display name. It displays correctly in the list itself but when my users export the list to excel but in excel the name is followed by extra characters displayed below:

    My question is there a way to hide those characters before the list is exported without having my users having to manually delete those characters in excel? I wanted to use the translate function to hide them but I cannot edit the display value so I wasn't able to do that.

    Any ideas on how to fix this would be gerat.



    • Edited by compuluv Thursday, May 14, 2015 4:23 PM
    Thursday, May 14, 2015 12:03 PM

Answers

  • 1. I don't see any picture as suggested.

    2. people picker fields are lookups, and as such WILL include additional information (such as the ID from the UserInfo table).

    you have several options...

    1. create a calculated column with the user name (I think? can't recall offhand whether people picker fields can be used in calculated columns)

    2. create a workflow to copy the user name into a text-only field

    3. use power pivot to merge the data list with the user info table, then display the name fields from the user info list


    Scott Brickey
    MCTS, MCPD, MCITP
    www.sbrickey.com
    Strategic Data Systems - for all your SharePoint needs

    • Marked as answer by compuluv Thursday, May 14, 2015 6:40 PM
    Thursday, May 14, 2015 3:28 PM

All replies

  • 1. I don't see any picture as suggested.

    2. people picker fields are lookups, and as such WILL include additional information (such as the ID from the UserInfo table).

    you have several options...

    1. create a calculated column with the user name (I think? can't recall offhand whether people picker fields can be used in calculated columns)

    2. create a workflow to copy the user name into a text-only field

    3. use power pivot to merge the data list with the user info table, then display the name fields from the user info list


    Scott Brickey
    MCTS, MCPD, MCITP
    www.sbrickey.com
    Strategic Data Systems - for all your SharePoint needs

    • Marked as answer by compuluv Thursday, May 14, 2015 6:40 PM
    Thursday, May 14, 2015 3:28 PM
  • Hi,

    People picker is an lookup column, so Id is exported during export, to my knowledge we cannot change that

    you can follow the workaround,

    1. Export the list, Query file dwonloaded and saved to local

    2. while opening the query file, data downloaded to excel file

    3. create formula column which trims the text after semicolon(;). hide the original column and keep the calculated column for displaying to user

    3. In that file, data connection is created automatically. you can see the connection details from menu Data>Connections

    4. select the connection and modify the properties to "refresh the data" while opening the file

    5. Save that file,  Your template is ready !

    Yes this file can be used as template.

    Next time while opening the file, User can see the latest data with desired values.

    Thursday, May 14, 2015 6:50 PM
  • Hi,

    I noticed that the additional extra chars (which is the id info) would only happen if you set the Person or Group field to allow multiple selections. Try disallowing the multiple selection and the additional extra chars would disappear.

    Thursday, August 6, 2015 2:43 AM