Answered by:
Removing the ID of a lookup column when exporting SharePoint list to Excel

Question
-
Answers
-
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
All replies
-
-
-
-
-
-
-
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
-
-
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.
-
-
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
-
-
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
-
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 ";#"
-
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.
- Edited by Hawthorne Pdx Tuesday, April 15, 2014 8:46 PM typo
-
-
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
-