2

I have a calendar All Events list which contains few fields, some from Managed Metadata and People presence lookup fields. When I try to use the Ribbon feature to export the list into an Excel, each of the field values have an id hash tag along with it.

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.

For instance: Attended by: field contains the list of Users with people presence enabled. When I export this field value into Excel, then each User gets copied into the sheet with their own unique # tag as in Doe, John;#39 Or if it's a Taxonomy Managed Metadata lookup field, then the export data will have their unique # tag in front of the value as in 74;#Ford

Please let me know if you can help. Do ask me freely if you have any questions or need further clarifications.

One suggestion was to try to copy the value into a calculated field as simple text replacing the # tags. But I cannot create columns based off calculation from existing lookup fields. Other suggestion is to create a macro in Excel workbook Book1, which can be coded to remove these hash tags.

I am hoping that there is some other simpler way to clean up the Excel data sheet. Please let me know. Thanks,

3 Answers 3

2

Answer here is create a cleanup macro in Excel that is then accessible to all spreadsheets via an .xlam addin that is stored in the XLSTART local machine directory.

This VBA code isn't pretty, but it works like a charm: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Remove_unneeded()

' Remove_unneeded Macro
' Removes hash setup from SharePoint exported workbook

Dim Again As Integer
Dim Again1 As Range

On Error GoTo Nope

Set Again1 = Cells.Find(What:="?;#", LookAt:=xlPart)
If Again1 Is Nothing Then
    Set Again1 = Cells.Find(What:=";#?", LookAt:=xlPart)
    If Again1 Is Nothing Then
        GoTo Nope
    End If
End If

Range("A1").Select
Again = 1

Do While Again < 5
    Set Again1 = Cells.Find(What:="0;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="0;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="0;#", LookAt:=xlPart)
    Loop

        Set Again1 = Cells.Find(What:="1;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="1;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="1;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="2;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="2;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="2;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="3;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="3;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="3;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="4;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="4;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="4;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="5;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="5;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="5;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="6;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="6;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="6;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="7;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="7;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="7;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="8;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="8;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="8;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:="9;#", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:="9;#", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:="9;#", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#0", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#0", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#0", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#1", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#1", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#1", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#2", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#2", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#2", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#3", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#3", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#3", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#4", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#4", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#4", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#5", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#5", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#5", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#6", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#6", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#6", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#7", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#7", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#7", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#8", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#8", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#8", LookAt:=xlPart)
    Loop

    Set Again1 = Cells.Find(What:=";#9", LookAt:=xlPart)
    Do While Not Again1 Is Nothing
        Cells.Replace What:=";#9", Replacement:=";#", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Set Again1 = Cells.Find(What:=";#9", LookAt:=xlPart)
    Loop
    Again = Again + 1
Loop

Set Again1 = Cells.Find(What:="#;#", LookAt:=xlPart)
Do While Not Again1 Is Nothing
    Cells.Replace What:="#;#", Replacement:=" | ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set Again1 = Cells.Find(What:="#;#", LookAt:=xlPart)
Loop

Set Again1 = Cells.Find(What:=";#", LookAt:=xlPart)
Do While Not Again1 Is Nothing
    Cells.Replace What:=";#", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set Again1 = Cells.Find(What:=";#", LookAt:=xlPart)
Loop

Set Again1 = Cells.Find(What:="#;", LookAt:=xlPart)
Do While Not Again1 Is Nothing
    Cells.Replace What:="#;", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set Again1 = Cells.Find(What:="#;", LookAt:=xlPart)
Loop

Set Again1 = Cells.Find(What:="#", LookAt:=xlPart)
Do While Not Again1 Is Nothing
    Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set Again1 = Cells.Find(What:="#", LookAt:=xlPart)
Loop

Set Again1 = Cells.Find(What:="; |", LookAt:=xlPart)
Do While Not Again1 Is Nothing
    Cells.Replace What:="; |", Replacement:=" |", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set Again1 = Cells.Find(What:="; |", LookAt:=xlPart)
Loop

Range("A1").Select
MsgBox "Extraneous hash data has been deleted."
Exit Sub

Nope:
    MsgBox "This is not the proper spreadsheet setup to run this macro."

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I also created an additional macro in the xlam with an iRibbon control to access this macro via a button on a custom ribbon in Excel and then edited the xlam UI xml to accomodate that. But that's window dressing as you can set up the UI any way you like once you have the macro ready to use.

You do have to turn on "Trust VBA Project Object Model" in Excel Options in the Trust Center, Macro Options. Unless of course you have the ability to digitally sign the addin.

0
1

The simplest "workaround" would be to maintain two different views probably a private view named as Export which is accessible to the list admins. This view can be a standard list view with the columns you want excluding the metadata column.

A second view can be created with the same sort and filter order to that of Export view. In this view just have the metadata column show up and no other columns from the list. So, once this view is loaded in the page , just copy all the values from the top to the bottom where the paging ends, and then paste it on a notepad. From the notepad you can then copy them all to a new column in the exported excel file and thereby you get all metadata values placed adjacent to their corresponding list items.

This is just a cheap workaround as I have also faced this problem before with both metadata based list and library. I just never had the skills to write an Excel macro so this turned out be a poor man's metadata column export. :)

2
  • Thank you for your input. Almost going in that direction myself. The reason being that it will be very hard to impossible to create a macro in Excel. I am looking at the individual cells and one has different pattern than the others. Even in one cell again, there are two or three different patterns - first lookup value has hash tags in front with a semi-colon; while the middle ones has extra hash tags...etc...
    – Ikram M.
    Jul 25, 2012 at 19:30
  • One thing though - I am not so sure about copying items from SharePoint into Notepad. Also, when dealing with two list of different nature - the copy & paste don't align the Excel cells correctly creating jumbled data. Even though your approach to copy the contents from SharePoint list into Excel sheet is naive, but copying all the data in one shot and pasting in Excel creates a more useable report. Thanks.
    – Ikram M.
    Jul 25, 2012 at 19:56
0

Instead of the Macro, you can also use the Left and Find formulas to rid yourself of the meta data for each column containing it. This is annoying if you have several columns, but it also works like a charm without adding macros. Another drawback is that you'll have to add the formula each time.

My solution:

Say cell A1 has the metadata and it looks like this: Bob#299

Your formula would be: =LEFT(A1,FIND(";",A1)-1)

Output: Bob

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.