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.
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.
- 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.
- 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.
- Now, click on the second cell of the column. And paste the commas again. Your cell should look like this now.
- 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:
- Now, after you close the bracket, you should press enter and this should be the output.
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.
|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.|
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.
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.
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.
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.
- Paste the data from your Excel column to the left box.
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.
- Select the cell where you have the list.
- After that, head to the “Data” section of the menu bar.
- On the menu bar, look for the “Text to Column” option and click on it.
- You should be met with this dialog box. Make sure to select “Delimited” instead of fixed width and then click “Next”.
- In this window, you should see something like this.
Click on the “Comma” button and click on “Next”
- You should see something like this.
Set the destination cell and then click “Finish”.
- After clicking “Finish”, the wizard should close and you should see an output similar to this.
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.