0

Using SP 2013 on-prem and created a custom list. One of the list form fields allows for multiple people to be added via our address book. Works great in SP, but a problem when I export the list into Excel.

The exported column now includes the # sign plus a number for each person & looks like this in the single cell:

Mark, Dan;#6;#Andreoli, Sandy;#19;#Easton, Robert;#15;#Allen, Steve;#45;#Miller, Joshua;#14

And it doesn't matter whether or not the field has the name or their unique email address, those additional characters associated with the address book show up.

I am trying to create a dashboard that refers to this column, but the additional characters are presenting a problem. I've even tried Excel formulas to try to remove them, but can't get there. HELP! Thank you!

Improve this question

2 Answers 2

Reset to default
0

The special characters are nothing but pound symbol and ID of that user in that site. That's SharePoint exports the user field to excel. It's by design and we cannot change it.

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

https://social.technet.microsoft.com/Forums/sharepoint/en-US/3397b6d2-a769-4f53-8370-3f75df75aeba/list-export-to-excel-remove-from-person-or-group?forum=sharepointgeneralprevious

Improve this answer
1
  • If my answer had helped you, kindly upvote and Mark it as answer, so that it will be helpful to others too
    – Karthik Jaganathan
    Jan 25, 2020 at 10:32
0

I also stumbled over this problem in SharePoint Online. I used the solution of Karthik Jaganathan but updated the Excel formula to replace the hashtag and the following ID:

=IF(ISBLANK(H2);"";MID(H2;1;FIND("#";H2)-1))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.