Excel Pro Trick: Convert Columns or rows to Comma Separated Lists in Excel using Formula - 2 Methods

Learn how to convert excel columns or rows to comma separated lists online in Excel using simple Excel formulas. As this approach is formula based, the output is dynamic i.e. Excel will automatically pull data and convert column to comma, semi colon or double quote delimited lists,

In this Excel tutorial, we are learning two methods to convert Excel data in columns to comma separated lists in one cell. Same concept applies to converting rows to comma separated lists in Excel.

First method uses Excel's newer TEXTJOIN function. TEXTJOIN formula is the simplest, shortest and does best to convert columns or rows to comma separated values.

Second method uses CONCAT and TRANSPOSE function to combine data in column to comma separated values in a single cell.

Remember, instead of comma, we can use a different delimiter to make lists. It can be comma, colon, semi-colon, apostrophe, dot or a space.

If you want to convert rows to comma separated lists, the first method remains the same with no change in it. However, for second method, skip the transpose function

Lets Excel!

Access or download practice workbook file for this Excel tutorial:

For more Excel tips, tricks and tutorials don't forget to subscribe to @LearnExceltoexcel channel.

Learn #Excel your way:
===================
--------------------------------------

Pick your favourite social page:

#excelsum #excel #exceltutorial #exceltips #excelformula #excelformulas #excelformulasandfunctions #excelformulaforjobinterview #excelformulatypes #textjoin #concat #concatenation #concatenate #exceltricks #exceldata #transpose

[jletier]

it does not work on my excel sheet it displays #Name? Error

[oghenefejiroagbi]

i tried the Concat, didnt work, but the "text join " command worked perfectly for numbers too

[RobPalmerrpalmer]

This short cut is AWESOME! Thank you!!!

[Karthikng]

there is a comma at the end while use CONCAT

[MuruganKuzhandhaivel-zwsg]

Will this work if it is numbers instead of text?

[abhishekshivhare]

U give practical workbook file for every short video ?

[prajwalshetty]

You not told to use Control+shift+enter