Converting a Range in Excel to a List With a Delimiter
נכתב על ידי ליאור לביא, עודכן בתאריך 26/10/2023
In this post, we will learn how to convert a range of cells in Excel (for example: A2:A8) into a comma-separated list without spaces (for example: Item 1,Item 2,Item 3). This list can be used in Salesforce search fields to find relevant records.
Steps to Follow
- Open a new Excel file.
- Enter values in cells A1 to A10.
- Click on any empty cell in the sheet.
- Enter the Transpose function and provide it with the range where you entered values:
=TRANSPOSE(A1:A10) - Add the desired separator, enclosed in double quotes, after the function you wrote in step 4:
In our example, the separator is a comma.=TRANSPOSE(A1:A10)&"," - Press the F9 key. Your formula should look like the following list, with the values you entered:
={"Lamp,","Chair,","Table,","Cabinet,","Sofa,","Computer,","TV,","Kettle,","Refrigerator,","Dishwasher,"} - Remove the curly braces { and } from the beginning and end of the list and replace them with regular parentheses.
=("Lamp,","Chair,","Table,","Cabinet,","Sofa,","Computer,","TV,","Kettle,","Refrigerator,","Dishwasher,") - Delete the comma after the last value in the list.
=("Lamp,","Chair,","Table,","Cabinet,","Sofa,","Computer,","TV,","Kettle,","Refrigerator,","Dishwasher") - Between the equal sign (=) and the opening parenthesis (, in the beginning of the formula, type the word CONCATENATE.
=CONCATENATE("Lamp,","Chair,","Table,","Cabinet,","Sofa,","Computer,","TV,","Kettle,","Refrigerator,","Dishwasher") - Press Enter.
- Right-click on the cell where you wrote the formula and select "Copy".
- Right-click on an empty cell in the sheet and choose "Values" under "Paste Options". Now, the cell will display all the list values separated by commas and without spaces. You can paste the content of the cell into Salesforce search fields, and the system will recognize our separator, the comma, as an OR operator in the search.
