Document Library containing Managed Metadata when exported to Excel has weird characters

Question

We have document library for storing assets in it. Document Library contains some of Managed Metadata lookup fields. When I try to use the Ribbon feature to export the list into an Excel, each of the Managd Metadata field values has an id hash tag along with it.

Data in the excel file gets exported for Taxonomy Managed Metadata lookup fields along with their unique # tag in front of the value as like "72;#AccepatbleUse"

This makes the excel sheet very much useless and unreadable.

Has anyone tried or come across this issue in SharePoint 2010 platform and how to export the data into an Excel without the id # tags.

Is there any OOB way to fix this issue? Thanks in Advance.

Answer 1

I don't think you can change the way that SharePoint writes the data to Excel. But once it's in Excel you can do a find/replace. Open the Replace dialog with Ctrl-h In the "Find What" box enter

*#

Leave the "Replace With" box empty and hit Replace All

The first two entries in the screenshot have already been cleaned with the Replace.

To make sure you don't replace any other # signs that may live somewhere in the data, you may want to select just the columns with the managed metadata values before you do the replace.