2

Has anyone come across this issue or dealt with ID numbers being exported from a lookup column? Is there a way that I can have the SharePoint list not export the numbers when I 'Excel export' the list?

List: enter image description here

Excel Export: The red boxed ID are the ones that I would not like to include in the Export which are the Lookup columns. enter image description here

All help on this issue would be appreciated.

2

I don’t think that there is any solution for this. The problem is, that SharePoint has to convert all fields to string which will produce “LookupValue;#LookupId” as can be seen on your screenshot.

  • Is there a way that this can be manipulated in Excel? - Like have a function/etc that will get rid of ;#(Numbers 1-1000) – SaintLouis Events Jan 9 at 16:42
  • 1
    I am sure you could do something in Excel, but @lukas-nespor is right. SharePoint wil export the data how it is seen a layer down. Same as if you were moving the other direction. Your data would have to match what SharePoint expects. – lazoDev Jan 9 at 17:25
0

There are workarounds for the issue:

  1. Turn off the “Allow multiple values” for the lookup column.

  2. If you must turn on the “Allow multiple values”, you could use the Substitute formula to remove the "#;#;" in the excel after exporting to the excel.

  3. You could create workflow to copy value from the lookup column to single line of text and set as the picture below.

enter image description here

And the result as below:

enter image description here

There is a similar post:

https://social.technet.microsoft.com/Forums/sharepoint/en-US/f3436b19-9184-4ed2-a4fe-4320cefeaaca/removing-the-id-of-a-lookup-column-when-exporting-sharepoint-list-to-excel?forum=sharepointgeneralprevious

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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