Home Tech Fix Best Nepal Finance

Convert a Column to a Comma-Separated List in Excel: A Step-by-Step Guide

👤 Diwas Poudel    🕒 06 May 2023    📁 FIX

Excel, we know it, we love it. It has been dominating offices since 1985. And rightfully so. It is a useful tool that offers a lot of configuration options relating to data entry and manipulation. Some features offered by Excel include Data Entry and Formatting, Formula and Functions, Charts and Graphs, Pivot Tables, Conditional Formatting, Data Validation, Macros, and Collaboration.

How to format it?

There are mainly two methods of doing this. One is the CONCATENATE function offered by Excel. This is the method that can be done inside of excel but can be quite hectic. On the other hand, there is an online tool for converting column data to a comma-separated list. Let’s see how we can use both of these methods and let’s also check how to convert comma-separated data to columns.

Inside Excel

Using the "Concatenate" Function

You can use the CONCATENATE function to combine the cells in a column. This is how you can do it.

  • Go to the cell where you want the list
  • Type the function =CONCATENATE(cell1, ",", cell2, ",", cell3, and so on.)
  • As you type in CON, you will be given a prompt with the CONCATENATE option. Double-click on it with your mouse.
    using concatenation function
    fig. using concatenation function
  • After you double-click there, you should be able to see CONCATENATE( on your screen. If you see it, simply click on the first cell on the column.
    using concatenate function with first parameter
    fig. The first parameter in Concatenate function
  • After this, you will have to type in (, ",",) without the brackets. This can get quite confusing, so I suggest you copy the text and paste it into the excel document. Your cell should now look like this.
    Using more parameter in concatenate function
    fig. Using more parameters in concatenate function
  • Now, click on the second cell of the column. And paste the commas again. Your cell should look like this now.
    concatenate function image 5
  • Repeat the steps until you have reached your desired cell in the column. I have already done that and this is how it looks for me:
    concatenate function with parameter
  • Now, after you close the bracket, you should press enter and this should be the output.
    Result of Concatenate function
    fig. Result

This method follows the Concatenate function provided to us by MS Excel. But, we have already seen that it’s quite a tedious process. If you want to use another process, you should read the rest of the article for the easier one. However, it should be kept in mind that the other process involves you having to get out of Excel and into another web application. Let’s take a look at it. 

Using the "TextJoin" function

You can also use the "TextJoin" function to join the cells in a column into a comma-separated list.This function was introduced in Ms Excel 2016.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Here:

delimiter The delimiter is the character or string that will be used to separate the text values. A comma, space, hyphen, or any other character or string can be used.
ignore_empty If TRUE is set, the method ignores empty cells and only combines non-empty cells else will include empty cells.
text1, [text2] The text values or ranges that you want to merge into a single string are listed here. The function can have up to 253 text values or ranges.

 

Here's how:
a. In an empty cell, enter the formula: =TEXTJOIN(",",TRUE,A2:A13), where A2:A13 are the cells in the column you want to join.

using TEXTJOIN function
fig. using TEXTJOIN function


b. Press "Enter" to see the result. This will create a comma-separated list of the cells in the column. Note that you can change the range A2:A13 to the range that contains your column of cells. 

Result of TextJoin function
fig. Result of TextJoin function

 

Out of Excel

Using  Convert Town

Convert Town is a useful web application I found while researching this article. On this website, you can simply paste the data from your Excel sheet into one bar and have the website automatically convert the data to a comma-separated list. Let’s take a look at it. 

  • Copy the column you want to convert.
    Copy the column you want to convert
    fig. Copy the column you want to convert

    Here, I am copying from A2 to A13. 
  • Go to https://convert.town/column-to-comma-separated-list. You should be able to see this.
    Goto Website as mention here
    fig. Goto Website as mentioned here
  • Paste the data from your Excel column to the left box.
    Result: Converted succesfully from COlumn to row
    Here, the data is automatically converted to a comma-separated list and copied to the clipboard as well. Now, you can paste the data back into Excel or any concerned document editor. 

These are the two methods that can be used to convert an Excel column to a comma-separated list. But if you ever need to take a look at how you can convert a comma-separated list to columns, take a look below at the rest of the article. 

How to convert a Comma-Separated List to a Column

The process of converting a list to a column is simple in Excel. Let’s see how it can be done. 

  1. Select the cell where you have the list.
  2. After that, head to the “Data” section of the menu bar.
    Goto Data Section
    fig. Goto Data Section
  3. On the menu bar, look for the “Text to Column” option and click on it.
    Select Text to Column Option
    fig. Select Text to Column Option
  4. You should be met with this dialog box. Make sure to select “Delimited” instead of fixed width and then click “Next”.
    Select Delimiter
    fig. Select Delimiter 
  5. In this window, you should see something like this.
    check in Comma Delimiter
    fig. check in Comma Delimiter

Click on the “Comma” button and click on “Next”

  1. You should see something like this.
    Check in
    fig. Check in the "General" Column data format

    Set the destination cell and then click “Finish”. 
  2. After clicking “Finish”, the wizard should close and you should see an output similar to this. 
You can see row are separated
fig. Row are separated successfully

Conclusion

There are multiple ways of converting a column in Excel to a comma-separated list. There is also a way of doing that the other way around. I hope that this article has helped you. If it has, feel free to buy me a coffee from the button below. Have a good day.

📧 Join our newsletter
📰 Latest Posts
📧 Join our Youtube Channel
Ourtechroom Main Logo

Ourtechroom.com is a technology-focused website that provides information and insights on various tech-related topics such as troubleshooting, emerging technology, games, gadgets, and fun facts. The website aims to offer valuable and informative content to its readers.
Contact : ourtechroom777@gmail.com