Convert Data/Column To Comma Separate List In Excel

The post demonstrates how to convert a column into a list of comma separated values.

Let’s see a scenario where we have to insert a condition in some SQL query’s IN operator, the values should be separated by comma and you have a large number of values.

Capture

As you could see above we have data in column A and the comma separated list that we require is in column B or in column E.

We will use simple concatenation operator “&” in excel to embed comma to convert column to comma separate list in excel.




Step 1

Insert the formula =A2&”,” in the cell B2 to insert comma  after the value.

Capture

Step 2

Now drag the formula downwards till the data is available to get the list of comma separated values.

Capture

Step 3




Now open a notepad text file and copy the values from excel and paste it into text file as shown below.

Capture

You have the comma separated values list to be used anywhere.

The values could directly be pasted into SQL editor if that is what you are looking for.

Hope this helped.

Share The Knowledge

Random Posts

  • Remove Duplicate In Excel

    We have Product data in column A and you can see there are a lot of duplicates values, we want […]

    Share The Knowledge
  • Delete Row Based On A Substring In Excel

    delete row in excel based on some string, excel delete rows based on substring, delete data in excel based on certain string, excel delete rows if the cell has certain string

    Share The Knowledge
  • Excel Formula To Repeat A Number Sequence

    In this post we will see how to repeat a number sequence in excel. To repeat a number sequence in […]

    Share The Knowledge
  • Highlight Mondays in Excel

    Highlighting the days in excel is a frequent operation.We come across several situation in which we need to highlight some […]

    Share The Knowledge

Leave a Reply