Exporting Share Point List to excel and showing # character and number after entries generated via Look Up columns

Question

Hi - I created a SP List where I am selecting other data points via the Look Up column option. When I export the data into excel, it includes the selected data points plus "#" and "number" (e.g. #12). It also brings Item Type and Path columns which I have not populated.

1. How can I export the data without the "#12" data?
2. How can I not export the Item Type and Path columns?

Thank you!

Mariya

Answer 1

Hi Mariya,

Thanks for posting back with the requested information and confirmed the scenario with us. We also appreciate that you spent your precious time doing those tests.

As you mentioned that the internal ID will only appear with multi-Lookup fields. We are truly sorry about the inconvenience caused by this.

Based on my test, the exported multi-lookup field is displayed correctly if I choose to export to CSV as shown below:

If I choose to export to Excel, the internal ID will appear in the exported sheet:

In your case, may I confirm if you export the SharePoint list to .iqy file or the csv?

If you are exporting the SharePoint list as .iqy, please try export it to csv file and see how it works, vice versa.

You can try below workarounds for the issue:

1. Export the list to CSV instead of Excel.
2. Turn off the “Allow multiple values” for the lookup column.
3. 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.
4. You could create workflow to copy value from the lookup column to single line of text.

For your reference: sharepoint export to excel shows internal id also for lookup field (microsoft.com)

Thank you for your patience so far.

Sincerely,

Rhoda | Microsoft Community Moderator

Answer 2

Dear Mariya_Dimitrova,

Good day! Thank you for posting in the forum. We’re happy to help you.

From your description, it seems that you are having issue with SharePoint list export, data added with “#12” and additional“ Item Type” and” Path columns” get exported as well.

If my understanding is right, about the question A, we have tested it on our side with the latest version of Excel and we cannot reproduce the same situation as yours. We will also suggest you refer to this article: Repair an Office application (microsoft.com) to repair office to check if it will help.

At the same time, to better understand your situation and narrow down this issue, could you provide the information below?

1. May I know if the other lists exports are with the same problem?
2. Does anyone else in your organization encounter the same issue with this list export?
3. Could you provide the product information and version of Excel? You can go to Excel > File > Account and provide the full screenshot of it.
4. Would you please provide the screenshot of the “#12” you encountered?

About question B, I'm afraid that there is no out-of-the-box feature which can do that currently. Sorry for the inconvenience.

As our product developers are willing to hear the users' suggestions, I kindly suggest you submit your feedback on the Feedback platformto our product developers so that they can know your requirements and ideas for product improvements.

We’d also recommend you hide the unwanted column in the exported excel files as a workaround for now.

You can save the exported excel list back to the site where the list is for further use to avoid seeing the unwanted column in new export as showed below

Thanks for your understanding and have a nice day!

Sincerely,

Rhoda | Microsoft Community Moderator

Answer 3

Hi - thank you for the information.

1. Some of my lists export with "#12" and some do not. I think this is the unique ID as it seems to show the order that they were created. I noticed that for the lists where I allowed only 1 selection when using the Look Up function, the #12 does not appear. Whereas when I allow more than one selections, #12 appears.
2. I do have the same issue with some of my lists.
3. Here you go:

4. Here you go: