These Excel concatenate multiple cells with comma formula examples:
- Help you join the contents of multiple cells using a comma (,) as a delimiter.
- Cover solutions for both of the following groups of Excel versions:
- Excel versions with the TEXTJOIN function (Excel 2019 and later, including Excel 365).
- Excel versions without the TEXTJOIN function (versions before Excel 2019).
Table of Contents
Excel Concatenate Multiple Cells with Comma Formula Template
Excel Concatenate Multiple Cells with Comma With the TEXTJOIN Function (Excel 2019 and Later)
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-cells-comma/
=TEXTJOIN(",",TRUE,CellsToConcatenate)
For these purposes, CellsToConcatenate is a reference to the cells you concatenate.
Excel Concatenate Multiple Cells with Comma Without the TEXTJOIN Function (Excel Versions Before Excel 2019)
The Excel concatenate multiple cells with comma (without the TEXTJOIN function) template below relies on several cells (1 cell for each original cell you concatenate). Out of these cells:
- The last cell's output contains the full string, concatenating the multiple cells with comma; and
- All other cells (excluding the last one) are helper cells.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-cells-comma/
'Concatenation cell 1
=CellToConcatenate1
'Concatenation cell 2
=ConcatenationCell1&","&CellToConcatenate2
'Concatenation cell 3
=ConcatenationCell2&","&CellToConcatenate3
'...
'Concatenation cell n
=ConcatenationCellN-1&","&CellToConcatenateN
For these purposes:
- ConcatenationCell# (ConcatenationCell1 to ConcatenationCellN-1) is a reference to the previous (n-1) helper cell you create.
- CellToConcatenate# (CellToConcatenate1 to CellToConcatenateN) is a reference to the applicable (original) cell to concatenate.
Excel Concatenate Multiple Cells with Comma Example Formulas
The Excel concatenate multiple cells with comma example formulas below join the contents of cells A6 to A25 using a comma (,) as a delimiter.
Excel Concatenate Multiple Cells with Comma With the TEXTJOIN Function (Excel 2019 and Later) Example Formula
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-cells-comma/
=TEXTJOIN(",",TRUE,A6:A25)
The first image at the top of this post shows the results I obtain with this Excel concatenate multiple cells with comma example formula.
Excel Concatenate Multiple Cells with Comma Without the TEXTJOIN Function (Excel Versions Before Excel 2019) Example Formulas
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-cells-comma/
'Concatenation cell 1 (cell C6)
=A6
'Concatenation cell 2 (cell C7)
=C6&","&A7
'Concatenation cell 3 (cell C8)
=C7&","&A8
'Concatenation cell 4 (cell C9)
=C8&","&A9
'Concatenation cell 5 (cell C10)
=C9&","&A10
'Concatenation cell 6 (cell C11)
=C10&","&A11
'Concatenation cell 7 (cell C12)
=C11&","&A12
'Concatenation cell 8 (cell C13)
=C12&","&A13
'Concatenation cell 9 (cell C14)
=C13&","&A14
'Concatenation cell 10 (cell C15)
=C14&","&A15
'Concatenation cell 11 (cell C16)
=C15&","&A16
'Concatenation cell 12 (cell C17)
=C16&","&A17
'Concatenation cell 13 (cell C18)
=C17&","&A18
'Concatenation cell 14 (cell C19)
=C18&","&A19
'Concatenation cell 15 (cell C20)
=C19&","&A20
'Concatenation cell 16 (cell C21)
=C20&","&A21
'Concatenation cell 17 (cell C22)
=C21&","&A22
'Concatenation cell 18 (cell C23)
=C22&","&A23
'Concatenation cell 19 (cell C24)
=C23&","&A24
'Concatenation cell 20 (cell C25)
=C24&","&A25
The second image (a GIF) at the top of this post shows:
- How I create the Excel concatenate multiple cells with comma example formula; and
- The results I obtain with this Excel concatenate multiple cells with comma example formula.
Excel Concatenate Multiple Cells with Comma Explanation
The TEXTJOIN function:
- Concatenates (among others) the items stored in multiple cells;
- Using a delimiter (you specify).
The TEXTJOIN function is available in Excel 2019 and later (including Excel 365). If you're working with one of these Excel versions, use the TEXTJOIN function to create an Excel concatenate multiple cells with comma formula.
Older Excel versions (before Excel 2019) lack an alternative that's as efficient as TEXTJOIN to create an Excel concatenate multiple cells with comma formula. The most common alternatives involve repeatedly entering:
- References to the cells you concatenate; and
- Commas.
For example, the ampersand text concatenation operator (&):
- Concatenates strings; and
- Returns a single string with the concatenated items.
Therefore, you can (theoretically) use the ampersand text concatenation operator (&) to create an Excel concatenate multiple cells with comma formula.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-cells-comma/
=CellToConcatenate1&","&CellToConcatenate2&","&CellToConcatenate3&","&...&","&CellToConcatenateN
Implementing this Excel concatenate multiple cells with comma formula template when working with larger data sets is:
- Time consuming;
- Tedious; and
- Error prone.
The Excel concatenate multiple cells with comma (without the TEXTJOIN function) template I explain above allows you to:
- Use the ampersand text concatenation operator (&) to create an Excel concatenate multiple cells with comma formula; while
- Addressing (to the extent possible) some of the limitations I describe above.
The TEXTJOIN function and the ampersand text concatenation operator (&) are not the only ways to concatenate items. You can find more Excel concatenate formula examples in the More Excel Concatenate Formula Examples section.
More Excel Concatenate Formula Examples
This formula example is part of a more comprehensive series of Excel concatenate formula examples.
- Excel Concatenate Strings: Click here to open.
- Excel Concatenate Number and String: Click here to open.
- Excel Concatenate Date: Click here to open.
- Excel Concatenate with Space: Click here to open.
- Excel Concatenate New Line: Click here to open.
- Excel Concatenate Double Quote: Click here to open.
- Excel Concatenate Multiple Cells with Comma: Click here to open.
More Excel Training Materials and Resources
You can find more Excel Tutorials (including other formula examples) in the organized Tutorials Archive: Click here to visit the Archives.
If you want to ease all your operations and processes, you may be interested in Someka's ready-to-use Excel and Google Sheets templates. Someka's template collections cover, among others:
- Project Management;
- Human Resources;
- Sales and Marketing;
- Inventory Management;
- Calendars and Timesheets;
- Budgets; and
- Invoices.
Click here to learn more about Someka's Excel and Google Sheets templates