Create Comma Separated Values From Excel Range

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.

Excel showing range and column with values from which we will create a list of comma separated values

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to create a list of comma delimited string from a range

Step 2

Insert a module in which we will write a VBA code to create list of comma delimited string.

Insert a module in the VB editor

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




VBA code to create comma separated values from cells

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.




Share The Knowledge

Random Posts