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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
2
  • 1
    Is there a way that this can be manipulated in Excel? - Like have a function/etc that will get rid of ;#(Numbers 1-1000)
    – SLE
    Commented Jan 9, 2019 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
    Commented Jan 9, 2019 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

Improve this answer
0

I know this is an old post but hopefully this can help someone. I set up some VB Code in Excel to remove the ID and characters. To use the code:

  1. Right click on the worksheet name and select "View Code"
  2. Double click on "ThisWorkbook" under "Project - VBA Project"
  3. Paste in the code below (replace "sheet1" with the name of the worksheet) and save the file as a .xlsm file. If you have more columns than "A:Z" you can increase that as well.

When you close and then reopen the file it will scrub the IDs.

Note that I added a line to refresh the data upon opening the workbook as well - if you don't do this and use the normal option to update the data upon opening the workbook it will scrub the IDs first, and then add them back in when it's refreshed. So make sure "Refresh data when opening the file" is unchecked under the connection properties.

Private Sub Workbook_Open()

'PURPOSE: Run Data tab's Refresh All function when file is opened
ThisWorkbook.RefreshAll

'PURPOSE: Scrub ID and #s
Worksheets("Sheet1").Columns("A:Z").Replace What:="#*#", Replacement:=" ", SearchOrder:=xlByColumns, MatchCase:=True
Worksheets("Sheet1").Columns("A:Z").Replace What:=";#*", Replacement:="", LookAt:=xlPart

End Sub
Improve this answer

Your Answer

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

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