Excel has a lot of features that make it unique and useful for data organization and analysis.
One of the many things that can be done in Excel is converting a column list to a comma-separated list.
The TEXTJOIN function, CONCATENATE function, or executing a VBA in excel are all ways that this can be accomplished.
In this article, we will be reviewing these three proven and easy methods by which you can convert a column list of data to a list separated by an operator.
Before we move on, let’s review some of the advantages of converting column lists to comma-separated lists in excel:
- For programmers, they may not necessarily need their dataset in a column-like form but will need it more in a comma-separated format. This is a feature in excel that is extremely favorable for programmers.
- In a project, data presentation can be required in any format and so having this knowledge of converting column lists to comma-separated lists goes a long way in improving data presentation skills.
- Also in data presentation, showing your data in both column list format and comma-separated list format goes a long way in proving versatility for the user and helps to present data to the consumers or clients in various preferred formats.
- When transferring or copying data from excel to Microsoft word, you might need to copy it in the comma-separated format because Microsoft word is not table-oriented unless you draw one, but excel layout is a spreadsheet that is in the form of many tables.
Having highlighted some of the advantages of converting column lists to comma-separated lists, let’s now go into the various methods that can be harnessed to make this a reality.
Here’s how to Convert Column List to Comma Separated List in Excel
Using the TEXTJOIN Function
With a defined delimiter, the TEXTJOIN function in excel joins several data from a row, span of cells, or column.
It is worthy of note however that the TEXTJOIN function is majorly available for use in excel for office 365, 2019 Excel, and then 2021 Excel packages.
Now please follow carefully as I walk you through how to use this function.
- Select a blank cell, such as cell C2 and type the formula
TEXTJOINto convert a column list to a separated comma-separated list (“,“,TRUE,A1:A8). - On applying this formula to the dataset, column A1:A8 will be converted to a comma-separated list, and “,” shows how the list will be separated.
Below is an illustration of a column dataset that was converted to a comma-separated list using the formula below:
=TEXTJOIN(",",TRUE,A2:A8)
Please take note of the TEXTJOIN function string, the bracket to be used is () and not {}.
The implication of imputing something else into the string is that it won’t work at all.
Though excel is not case sensitive if the text strings are used wrongly, there will be no result.
Using the CONCATENATE Function
Like I said earlier, the CONCATENATE function is also very useful in converting a column dataset to one separated by a comma.
The following steps are applicable when working with this method.
Step 1: Mark a blank cell adjacent to the list’s first data, such as C2, and type =CONCATENATE(TRANSPOSE(A2:A8)& ”,”) into it. The column A2:A8 will be converted to a comma-serrated list, and the separator “,” will be used to separate the list.
Step 2: Now the next step is to mark TRANSPOSE(A2:A8)&”,” and then press F9.
On pressing F9, the TRANSPOSE(A2:A8)&”,” that you marked earlier will change to your dataset separated by commas but still within two brackets. The two brackets look like this ({}).
Step 3: You then erase the curly {} bracket and tap the enter button on the keyboard.
On doing this, you will be shown your dataset in a list separated by a comma accordingly.
Following sequentially all the steps above, we arrive at a destination of significance, that is, seeing all our column values being converted to a cell list separated by a comma.
The pictorial representation of the steps highlighted so far is seen above.
Please note that the CONCATENATE function only works when it’s properly imputed.
Take note of all the symbols in the text string and also pay attention to the marking of the TRANSPOSE(A2:A8)&”,”.
It’s meant to be marked alone and not the brackets with it, if the brackets are marked or highlighted with it, it won’t execute.
Using Visual Basic for Applications (VBA)
Another very useful method of converting column lists to comma-separated ones in excel other than the CONCATENATE function is using VBA. Highlight the dataset range you wish to convert, and follow these simple steps.
Step 1: To open a Microsoft visual basic for application window, press down the ALT key and press F11 on the keyboard.
Step 2: To paste the VBA string in the module, click on insert>module
Below is the VBA string, you can copy it into your module from here
Sub ChangeRange()
'Updateby20220309
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Excel Book"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub
Step 3: After you have copied this string into your module, you then press the F5 button and click on “Run” to run the string.
Step 3: You then see the column list you want to convert displayed (which was previously highlighted before running Microsoft VBA) as a dialog on the screen.
Click on the Excel spreadsheet to verify the highlighted dataset range is correctly shown in the dialog box.
Step 4: After clicking the OK button, you then proceed to select the cell you want your converted list to appear on the spreadsheet.
Step 5: Finally, you click on the OK button once again, and then you have your column list all converted to a comma-separated list.
Now please take note that the VBA method can be a bit stressful if you do not write the code correctly, you will have to trace your work and see where the mistake is.
A single mistake of not putting “&” where it’s meant to be will cause a problem and the program will not run at all.
To avoid error and stress, copy the one I have provided in this piece and then paste it into your VBA module following the other steps meticulously, you will most surely get results.
Like I said earlier, programmers may like this method more, but then it can be used by all as long as the steps are followed.
Using Kutools For Excel
Kutools for Excel is a powerful add-in for Excel with the capability of simplifying various complex tasks using its advanced features. It is not available in Excel by default. Just as we have converted column lists to comma-separated lists in Excel using various methods, we can also use Kutools for Excel to obtain the same result without losing any data. This can be achieved following these simple steps:
Step 1: Highlight the dataset range you wish to convert, click on the following in the order: Kutools – Merge & Split – Combine Rows, Columns or Cells without Losing Data.
This opens a dialog box where you can configure how to combine rows and columns.
Step 2: In the dialog box, make the following selections:
- Click on Combine rows
- Click on Other separator, and type in a comma “,” in the following box
- Click on Delete contents of combined cells
- Click on Apply
- Click on OK
Following sequentially the simple steps above, all our column values become converted to a cell list separated by a comma. Using Kutools, this can also be reverted to columns again if needed.
Conclusion
You are now knowledgeable in converting column lists to comma-separated lists in excel.
These four simple and easy-to-model methods have been highlighted and thoroughly explained: the TEXTJOIN function method, the CONCATENATE function method, VBA code string, and Kutools for Excel.
Whichever of these methods, when correctly applied, will give a similar result.
