locked
Managed Metadata Fields have Term ID I Export to Excel RRS feed

  • Question

  • Hello all,

    When I export to Excel from a list, any Managed Metadata fields appear to have the term ID included (ex. ;#16). This is problematic, especially for BI purposes. Anyone have any ideas?

    Thursday, December 16, 2010 9:22 PM

Answers

  • This is a known problem but is by design based on how managed metadata works. It will also be a problem with lookup fields. Excel uses rpc to execute the iqy file and all the fields use their ToString methods to generate the values. I don't know of a work around except with special formatting in Excel.
    Blog | SharePoint Field Notes Dev Tool | ClassMaster
    • Marked as answer by Stanfford Liu Friday, December 24, 2010 6:12 AM
    Thursday, December 16, 2010 10:48 PM

All replies

  • This is a known problem but is by design based on how managed metadata works. It will also be a problem with lookup fields. Excel uses rpc to execute the iqy file and all the fields use their ToString methods to generate the values. I don't know of a work around except with special formatting in Excel.
    Blog | SharePoint Field Notes Dev Tool | ClassMaster
    • Marked as answer by Stanfford Liu Friday, December 24, 2010 6:12 AM
    Thursday, December 16, 2010 10:48 PM
  • The number that you see there is the list item id from the corresponding entry in the hidden taxonomy table.  Access displays managed metadata fields the same way.  In either Excel or Access, it is possible to use formulas to strip the unwanted characters.

    Interestingly enough, the format you see is the required format if you want to use SharePoint Designer workflow to assign a value to a managed metadata field.

    Friday, December 17, 2010 2:21 PM
  • Has anyone found a work around without having to create a formula column or Macro inside Excel to strip of these unwanted termids.

    I am very much interested in knowing of any solution.

    It also seems that I cannot create a calculated column based of these lookup fields. Thought I could create a formula to truncate the value to desired result when displaying the view and exporting to Excel.

    Thanks,

    Tuesday, July 24, 2012 8:34 PM