The ability to join cells with a comma in Excel is a great way to quickly and easily create a list of items or combine data from multiple sources. It can be used to create a list of items from a single cell, or to combine data from multiple cells into a single cell. This feature can be used in a variety of ways, from creating a list of items to combining data from multiple sources.
In this article, we will discuss how to join cells with a comma in Excel and the various ways this feature can be used.
Table of Contents
Use ampersand operator and and SUBSTITUTE function
Ampersand (&) operator is used to join values of defined cells. If the number of cells is limited, use this operator is fast.
After connecting cells with & operator, we use SUBSTITUTE function to replace certain text with a comma. In our case, it is blank space.
=SUBSTITUTE(A2&" "&A3&" "&A4&" "&A5&" "&A7&" "&A7&" "&A8," ",", ")Result
Shanelle, Lily, Garrett, Madeline, Christophe, Christophe, JoeUse TEXTJOIN function
This is a new function available in Office 365 and Excel 2019. It can concatenate a range of cells with a delimiter.
=TEXTJOIN(", ",TRUE,A1:B3)Result
First Name, City, Shanelle, South Vadatown, Lily, North TitusvilleNote that if any of the cells you are concatenating contain text or numbers that Excel recognizes as dates, Excel will convert them to date format. To prevent this, you can format the cell as Text before concatenating the cells.
