Sign in
 

none
Removing the ID of a lookup column when exporting SharePoint list to Excel RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I export a list in Excel and I noticed the lookup column contains an ID together with the name (e.g. John Doe, #12). Is there a way I can avoid exporting the ID in Excel?

    Thanks.

    Tuesday, January 8, 2013 4:18 PM
    |

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    yes, because lookup columns can't be used in calculated column ,and for the moment I can't give you a solution to calculate substring directly in workflow.

    If you get a formula to do it directly in the workflow, you don't need the secondary column

    But in my example, yes

    first column is populated by wkf with lookup column's value

    and the second mandatory column is a calculated column, based on the first one

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by jseller138 Monday, January 14, 2013 3:35 PM
    Saturday, January 12, 2013 12:04 PM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jseller,

    Is John Doe and 12 in a separate column or both in the same column?

    Regards

    Tuesday, January 8, 2013 4:28 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Same column. The lookup also allows multiple values.

    Tuesday, January 8, 2013 4:43 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    You might be able to do this with code which is why past my ability :( (Only know SQL/Html)

    Anyone else shed some light?

    Can you recreate the lookup time and have them in separate columns? Makes life much easier!

    Tuesday, January 8, 2013 4:45 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    What do you mean by lookup time?
    Tuesday, January 8, 2013 4:47 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Sorry typo, I mean recreate the lookup table
    Tuesday, January 8, 2013 4:48 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    I think I misunderstood your question. They are separate columns. I;m not sure where the ID is coming from. It might be an index of some sort.
    Tuesday, January 8, 2013 8:28 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    LookUp Field Values have a string representation of “(ID);#(VALUE)”, i.e. “2;#Second Entry”.

    If you export the list in excel you could create a formula or macro that split/copy only the value in a new column and then hide the real one.

    Try the following blog post for same samples:

    http://sharepoint-freak.blogspot.it/2011/04/when-you-use-lookup-fields-or-any.html

    http://excel.shilor.org/2010/07/export-sharepoint-2007-list-with-multi.html

    Marco Rizzi @Avanade Italy http://blog.marcorizzi.com

    Tuesday, January 8, 2013 9:00 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the response!

    So does that mean that it's not possible to export a lookup field in excel without the ID?

    Friday, January 11, 2013 6:46 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    create in your list a new calculated column as

    = =LEFT(YOUR_LOOKUP_COLUMN,FIND(",",YOUR_LOOKUP_COLUMN))

    If ',' exist only one time in YOUR_LOOKUP_COLUMN it's ok, if not, replace it with '#'

    This way you will have a 'clean' column!

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Friday, January 11, 2013 7:41 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    The lookup column does not appear in the list of columns to insert so I pasted this formula:

    =LEFT([lkp_MID],FIND("#",[lkp_MID]))

    However, I get a Server Error in '/' Application.

    Friday, January 11, 2013 8:23 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    right. I tried with a standard column

    This is the solution for you

    So for a lookup column, you need a new step

    1. Create a new column name it col_wkf

    Create a wkf attached to your list, which will start when an item is added or changed and which will fill col_wkf with YOUR_LOOKUP_COLUMN

    And because , the value stored in lookup column, is like: "ID# text", the right formula to use is this one

    =RIGHT(col_wkf,LEN(col_wkf)-FIND("#",col_wkf))

    This will fix your issue. Best regards

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Proposed as answer by Hawthorne Pdx Monday, April 14, 2014 5:35 PM
    Friday, January 11, 2013 9:28 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    So I have to create 2 columns for this? one that the workflow will populate and the other one is a calculated column based on the first?
    Friday, January 11, 2013 10:39 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    yes, because lookup columns can't be used in calculated column ,and for the moment I can't give you a solution to calculate substring directly in workflow.

    If you get a formula to do it directly in the workflow, you don't need the secondary column

    But in my example, yes

    first column is populated by wkf with lookup column's value

    and the second mandatory column is a calculated column, based on the first one

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by jseller138 Monday, January 14, 2013 3:35 PM
    Saturday, January 12, 2013 12:04 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    May not be exaclty what your issue was and I wasn't able to avoid exporting the ID, but I suppressed displaying the ID numbers by adding a field to the excel output that looked something like this:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Table_owssvr_1[[#This Row],[Fieldname]],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),";#;#","; "),";#","")

    This is just replacing the number with nulls, replasing the "#;#;" with commas, then removing the remaining ";#"

    Wednesday, July 10, 2013 3:08 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Another 2 potential solutions:

    1.  Turn off multiple value option for lookup column. 

    2.  If you need to have multiple values, another workflow option is to have workflow column set info pulled to string. 


    Tuesday, April 15, 2014 8:45 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I tried the substitute formula, and it worked well in my case.

    Tuesday, March 17, 2015 4:27 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    May not be exaclty what your issue was and I wasn't able to avoid exporting the ID, but I suppressed displaying the ID numbers by adding a field to the excel output that looked something like this:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Table_owssvr_1[[#This Row],[Fieldname]],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),";#;#","; "),";#","")

    This is just replacing the number with nulls, replasing the "#;#;" with commas, then removing the remaining ";#"

    This one worked great. I then created 3 more columns (more if you will have more than 3 IDs in the source column) with the following formulas:

    Column 1 (L for me)

    =LEFT(InsertColumn&CellWhereProvidedSubstituteFormulaWasUsedInMyCaseK2,SEARCH("; ",K2)-1)

    Column 2 (M for me)

    =RIGHT(K2,LEN(K2)-SEARCH("; ",K2)-1)

    Column 3 (N for me)

    =RIGHT(M2,LEN(M2)-SEARCH("; ",M2)-1)

    Thanks RBadics!




    • Edited by Zeedrummer Monday, August 24, 2015 6:48 PM Bold and Italics not showing
    Monday, August 24, 2015 6:46 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    This solution worked for me. Been working on it all day - thanks!!

    LVJC4EVR

    Tuesday, December 26, 2017 8:50 PM
    |