sharepoint export to excel shows internal id also for lookup field

Question

_{Monday, February 4, 2013 2:01 PM}

I have created a sharepoint list with lookup fields. When i take 'export to excel' then in the excel file it shows internal id also of the lookup data. How to get rid of this? please advise.

All replies (8)

_{Monday, February 4, 2013 3:11 PM ✅Answered}

I have created a sharepoint list with lookup fields. When i take 'export to excel' then in the excel file it shows internal id also of the lookup data. How to get rid of this? please advise.

I created a list and another with a lookup field pointing to the first list. Filled both lists with some values and exported both but I cannot reproduce your problem. The exported excel files only contain the title of the lookup fields.

There is an option when you add/edit a lookup field that allows you to add additional fields to the column you're adding. You'll see it below the name of the column you want to lookup and it'll have ID, Title, Modified, Created, etc. Are any of these checked? I added the ID field and got an extra column in my spreadsheet that was ColumnName:ID.

_{Monday, February 4, 2013 2:34 PM}

Hi

In Excel export only the columns of the view.
Clear the columns that you want in the view and then exported to Excel

Miguel de Hortaleza

_{Wednesday, May 1, 2013 9:40 PM | 1 vote}

I decided to deal with the formating after the export with a macro in excel to remove the unwanted characters.  Here's the macro I recorded.  First it replaces the numbers in between multiple names with a semicolon and then it deletes the numbers after the last name.  Hope it helps.

Sub NameClean()'' NameClean Macro'    Cells.Replace What:=";#*;#", Replacement:="; ", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Cells.Replace What:=";#*", Replacement:="", LookAt:=xlPart, SearchOrder _        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=FalseEnd Sub

B

_{Friday, April 25, 2014 7:31 AM}

Hey Bil,

I did the same way you have explained. The only change is that I selected the "Allow multiple values" check box in the Look up field settings. Now if I export the data, I am seeing the internal id from the parent list.

Could you pls suggest any alternatives?

Thanks Sandy

_{Thursday, May 15, 2014 1:38 PM}

On SP go to List Settings, Click on Column you want to modify, and under Show List: choose Name.

_{Friday, May 16, 2014 1:39 PM}

I don't see this "Show List" or the "Name" here.

Thanks Sandy

_{Saturday, May 31, 2014 10:13 PM}

1 On SP Page Click on List Name(your List Name)

2 On Top Left Browse Item List, Click on list to bring List Menu.

3 Top Right Find List Settings and Click there.

4 Scroll Down till you see Columns

5 Find your Name Field there and Click to edit

6 Play whatever you want

_{Thursday, February 4, 2016 7:38 AM}

Like wise others, I used macro to clean lookup & person\group columns.

Private Sub CommandButton1_Click()
    
    'Format Person Group Column
    Call FormatColumn("OtherColumns", "AB")
    
    MsgBox ("Formatting Lookup & Person - Group column completed.")
End Sub

Sub FormatColumn(ByVal Shet As String, ByVal col As String)
    Dim flagValueChanged As Boolean: flagValueChanged = False
    Dim strPattern1 As String: strPattern1 = "#\d+;"
    Dim strPattern2 As String: strPattern2 = "#\d+"
    Dim strPattern3 As String: strPattern3 = "#"
    Dim strReplace As String: strReplace = ""
    Dim regex As New RegExp
    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
    End With
    
    'process for all items in the column
    
    rowscount = Sheets(Shet).Range("A1048576").End(xlUp).Row
    'i = 16
    For i = 2 To rowscount
        flagValueChanged = False
        currentValue = Sheets(Shet).Cells(i, col)
        'MsgBox (currentValue)
        
        'format Pattern1
        With regex
           .Pattern = strPattern1
        End With
        
        If regex.Test(currentValue) Then
           currentValue = regex.Replace(currentValue, strReplace)
           flagValueChanged = True
           'MsgBox (currentValue)
        End If
        
        'format Pattern2
        With regex
           .Pattern = strPattern2
        End With
        
        If regex.Test(currentValue) Then
           currentValue = regex.Replace(currentValue, strReplace)
           flagValueChanged = True
           'MsgBox (currentValue)
        End If
        
        'format Pattern3
        With regex
           .Pattern = strPattern3
        End With
        
        If regex.Test(currentValue) Then
           currentValue = regex.Replace(currentValue, strReplace)
           flagValueChanged = True
           'MsgBox (currentValue)
        End If
        
        'update formatted value
        If flagValueChanged Then
           Sheets(Shet).Cells(i, col) = currentValue
        End If
        
        'MsgBox (currentValue)
    Next i
    
End Sub