+ Start a Discussion
Steph12345Steph12345 

Importing from Excel into a multi-select Picklist?

Is there a way to import excel data into a multi-select Picklist?

I need to a) have several values in my excel field which will map to different existing options on the Picklist, and b) not overwrite any existing selections in the Picklist if I am updating a pre-existing Contact.

At the moment it just imports whatever I have into the excel field as a new option on the picklist, overwriting whatever was previously selected.
MPIMPI
Multi-select Picklists in the Excel Connector currently function as follows:

Item_1;Item2;Item3

To update custom excel info into this format you will have to make sure of the following:

1) Your options in Excel are the same text values as the ones in your Multi-picklist (otherwise they will be added as temporary values as you noticed). You can do this with a simple find replace.

2) Your items need to be semi-colon separated. There is no join function in Excel for a range of cells, but with a bit of formula work and IFs you can use the CONCATENATE function to achieve this (only applicable if your multi-picklist values in excel are in different cells).

3) You need to join your new values with the existing ones in that picklist. The way I usually accomplish this is create a dummy column at the end containing my new values already formatted with semicolons. Then I use a formula to combine the connector data column and this new values column in yet another column. Then I copy my newest "result" column and do edit->paste special->values into the data column replacing the old with the combination of old and new.

Whenever I work with the connector I almost always have to do data processing first with formulas.

Hope this helps.
- Yori
Tim McDTim McD

I am trying to do something very similar, and wondering if you can provide me with some help regarding the excel formula.

For each name, I need an output the CONCATENATEs the name of the column if there is an X in that column, seperated with semicolons.  (The Desired Output column).  I can then map that value to the associated multiselect picklist in Salesforce.

 


                  A              B           C          Desired Output

Name       X              X                           A;B

Name2     X              X            X            A;B;C

Name3     X                            X            A;C

 

I really do appreciate any help you can give,

Tim

Peter_BPeter_B

Hi - was there any answer to your problem?

 

thanks,

Peter

LibbyKLibbyK

=if(B2="X","A;","")&if(C2="X","B;","")&if(D2="X","C;","")

John PlunkettJohn Plunkett
For the import to work, can the desired output have a ";" at the end? If not, is there alternative approach?
Rick UptonRick Upton
John Plunkett, here's an update to LibbyK's formula that will take care of the trailing semicolon by adding a period to the end of LibbyK's string and then replacing the trailing semicolon and period with nothing.
=SUBSTITUTE(CONCATENATE(IF(B2="X","A;","")&IF(C2="X","B;","")&IF(D2="X","C;",""),"."),";.","")
Rick UptonRick Upton
Check out the Xappex XL-Connector (formerly Enabler4Excel) product at https://www.xappex.com/ It is a great product for downloading Salesforce records into Excel including multi-select picklist fields, updating multi-select picklist values that are brought from Salesforce using a VBA popup window, and then updating or inserting the records from Excel into Salesforce. Unfortunately the multi-select picklist field popup support is only available for Windows.
Rick UptonRick Upton
John Plunkett and LibbyK, I updated the formula again to take care of the case when all cells are blank (previously the formula would return a period if all cells were blank):
=IF(AND(ISBLANK(B5),ISBLANK(C5),ISBLANK(D5)),"",(SUBSTITUTE(CONCATENATE(IF(B5="X","A;","")&IF(C5="X","B;","")&IF(D5="X","C;",""),"."),";.","")))
Matt Foster..ingMatt Foster..ing
I use this excel add in called Kutools, Advance combine rows
- https://www.extendoffice.com/product/kutools-for-excel/excel-combine-duplicate-rows.html