How to convert an Excel column into a comma separated list?


There are several instances when we need to make a comma separated text values from an excel column. There are different but pretty lengthy process that you can find, which will eat a no. of your valuable time to process the same.

 

So, what to do? Today we are going to learn a quick and easiest way to copy a column of data to a comma separated list. Continue reading to know more.

 

Who wants to give his effort to complete a hard/difficult job? Let's for example, if you have an Excel sheet having a column of huge data and you want to put them in a comma separated list, what will you do? You may think to manually put a comma (',') after each cell and move up the next cell of the column! Or, if you know little bit of excel functions, you may think to try that! If you are a dumb, you just pass on to your junior. Isn't it?

 

At that point, do you ever think that it could be possible by an expert in few seconds/minutes and will utilize a no. of free times showcasing that the work is in-progress?

 

If you don't want to do that and want the job done by yourself, here's the easy steps:

    • Open your excel sheet and select the cells of the column that you want to process. Select only one column. Copy the selected cells.



    • Now open word document, right click on it and from the context menu, select 'Paste Options' as 'Keep texts only'. This will put the selected column in the word document but in plain text format.



    • Now press 'CTRL + H' to open the 'Find and Replace' dialog. As shown in the below screenshot, enter "^p" in the 'Find what' field and enter "," (comma) in the 'Replace with' field. Now click on the 'Replace All' button.



    • Within a few moment, you will see that the cell values of the column has been switched to a single line having ',' (comma) as the separator.



Done? Wasn't it easy enough to convert a column of huge excel data to a line of comma separated values? How much time it takes for you to achive this? If you know a better solution, do let us know. We will love to hear that. Have a great day ahead!

 

 


If you have come this far, it means that you liked what you are reading. Why not reach little more and connect with me directly on Twitter, Facebook, Google+ and LinkedIn. I would love to hear your thoughts and opinions on my articles directly. Also, don't forget to share your views and/or feedback in the comment section below.

4 comments

  1. You could use a TextJoin formula in excel.
    Syntax: -
    =TextJoin(",", True, A1:15)

    ReplyDelete
    Replies
    1. I could not find any 'TextJoin' formula in Excel.

      Delete
    2. I think its introduced in Excel 2016.

      Delete
    3. I am using Excel 2016. I too could not find it there.

      Delete

 
© 2008-2016 Kunal-Chowdhury.com - Microsoft Technology Blog for developers and consumers | Designed by Kunal Chowdhury
Back to top