none
List Export to Excel Remove ;# from Person or Group RRS feed

  • Question

  • User Need: I have a SharePoint list of report requests. The list has a column for requester(s); person or group column with multiple selections enabled. Whenever I export the list to Excel, the records display with odd delimiter characters:

    Mary One;#446;#Bob Smith;#317;#Bilbo Baggins;#273

    I am exporting the data to Excel to create a PivotTable. The spreadsheet is then going to use Excel Services in SharePoint to display in a web part. The report will show total reports completed each month so it needs to refresh when the user views it. 


    Notes:

    • I can make a workflow, I just need a push in the right direction for this specific case. 
    • I explored macros and VBA, but am not sure how that is impacted with Excel services and refreshing in a web part. 
    • Once this is done, the user should just open the page and see the current trends. 
    • Dashboard designer is not configured at this company so I can't use that. 
    • I am open to alternatives on how to make this work. 

    Any suggestions on how to clean this up? 


    • Edited by Rob Baldwin Wednesday, February 20, 2013 6:53 PM The bottom text was misformatted
    Wednesday, February 20, 2013 6:52 PM

Answers

All replies

  • Hi Rob,

    I had the same issue with PowerPivot and excel.

    To get around this I used a workflow.

    On item Added, edit list item, set column value = "User Column" as string. This will then enable the Pivot Table to display the correct name and no the name with the user ID.

    Regards
    Pieter
    Thursday, February 21, 2013 9:06 AM
  • Hi,

    I understand that you want to remove the number and ;# in excel file. You can use formulas to achieve this.

    =SUBSTITUTE(H2, ";#", " ") Change H2 to the column which contains the user names. Then the formual will replace the ‘;#’ with a blank space.  In order to remove the numbers, it would be more steps to do. You need to know the exact number and then replace it using formula.

    For more information, please refer to this site:

    Export sharepoint  list to excel shows ids in people column: http://social.technet.microsoft.com/forums/en-US/sharepointgeneralprevious/thread/4bf70c99-2b0c-4162-b8e5-fe4e591db1fc

    Thanks,

    Entan Ming


    Entan Ming
    TechNet Community Support

    Thursday, February 21, 2013 9:10 AM
    Moderator