In this post we will see how to create a list of comma separated values from an excel range.
Suppose we have a range in excel with values in it and we would like to create a list of comma separated values and store it in a cell, there are some situations in which we are required to do this kind of operation e.g. we may have to provide argument in IN operator in SQL with the comma separated values that we create like this.
Below is the data which shows the range of values from which we want to create a list of comma separated values and store it in a cell.
Range A1:A10 shows the value from which we want to create a list of comma delimited values and store it in cell B1.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to create list of comma delimited string.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub Create_Comma_Sep_Values()
Comma_Sep_Values = Join(Application.WorksheetFunction.Transpose([a1:a10]), “,”)
Range(“B1”).Value = Comma_Sep_Values
End Sub
This vba code is creating scanning the range inn excel , creating a list of comma separated values from the values in the cell and storing the comma separated string in cell B1.
Hope this helped.
