ExcelInsider Team
In a spreadsheet, there could be a lot of columns and rows. The data gets cluttered and sometimes, we find that we do not need a few columns or cells. Those multiple cell data could be written in one cell. In this article, we will learn four ways to combine multiple cells into one separated by comma.
➤ Select the output cell where you want the combined data to show up.
➤ Write this formula:
=CONCAT(A2,”,”,B2)
➤ Replace A2 and B2 with your input cells and press Enter.
➤ Autofill the other cells in the column if you want.
This method is quite easier to implement as long as the cells you want to merge are limited. But if you have a large number of cells, writing all of the cell names in a formula would get tedious really fast. In this article, we will share four methods, including one with VBA code. Therefore, whether you are a beginner or an expert, stick around and learn something.
Download Practice WorkbookUsing the CONCAT Function
In the practice dataset, we have the sales performance data of multiple salespeople. However, there are some salespeople who have the same name but are from different regions. It would be better to combine the salesperson’s names with regions to recognize them properly. Let’s do that.
Microsoft officially suggests this method to combine multiple cells into one.
Steps:
➤ Choose the cell where you want your combined data to be.
➤ Enter this formula:
=CONCAT(A2,”,”,B2)
➤ Autofill the other cells by dragging the (+) signed pointer at the lower right corner of the first cell.
Using the Ampersand (&) Operator
Another simple way to do the job is to use the ampersand (&) operator. You will still have to use a formula, but there is no harm in learning another method in case you need it someday.
Steps:
➤ In the cell where you want to combine the data, write this formula:
=A2&”,”&B2
➤ Autofill the other cells to fill the entire column.
Using the TEXTJOIN Function
In the methods described above, the cells that need to be combined require you to write every cell name one by one. But what if you wanted to combine a lot of cells? That would take a long time. Fortunately, the TEXTJOIN function allows you to join a lot of cells without having to write all the names of the cells.
Steps:
➤ You know the drill at this point. Select the output cell, and write this formula:
=TEXTJOIN(“,”,TRUE,A2:B2)
➤ Replace A2 and B2 with your cell range, and autofill the other cells if you need to.
Using VBA Code
If all those functions don’t appeal to you, I have another solution that might. This time, we are going to use VBA code to write our own function that only needs the range of the data, no comma sign or anything else.
Steps:
➤ Press Alt+F11 to open the Microsoft Visual Basic for Applications window. This is embedded into Microsoft Excel, and you don’t need to install any other program.
➤ Go to Insert > Module from the menu at the top.
➤ The code editor will open. Write this code in the editor:
Function CombineText(InputRange As Range) As String
Dim Cell As Range
Dim ResultString As String
Dim DefaultSeparator As String
DefaultSeparator = ","
ResultString = ""
For Each Cell In InputRange
If Trim(Cell.Text) <> "" Then
ResultString = ResultString & Cell.Text & DefaultSeparator
End If
Next Cell
If Len(ResultString) > 0 Then
CombineText = Left(ResultString, Len(ResultString) - Len(DefaultSeparator))
Else
CombineText = ""
End If
End Function
➤ Go back to your original excel window. Now select the output cell and write this formula:
=COMBINETEXT(A2:B2)
➤ Replace A2 and B2 with your range of data, and autofill other cells.
Frequently Asked Questions
How to combine cells in sheets?
From google sheets, select the cells you want to combine. Go to the top menu, and select Format > Merge cells. Then google sheets will ask you to customize the way you want your cells to be merged. Follow the instructions from there.
How to make a comma separated list in Excel?
Select the column that includes the list you want to modify. Then from the Data tab, select Text to Columns. A wizard will open. From there, select Delimited, then select Comma from the next page. After finishing, you will have your list.
How do I convert Excel data to comma separated Text?
After completing your workbook, go to the File menu and select Save As. In the lower field of the file’s name, select CSV (Comma delimited) (*.csv) and click Save. The new file will be saved as a comma-separated text file.
How to merge 2 columns into 1 in Excel?
Use this formula:
=CONCATENATE(A1, B1)
Change A1 and B1 to the first cells of the column you want to merge.
How do I combine multiple cells into one cell with multiple lines in Excel?
Select the cells you want to merge. Then from the Home tab, go to Merge > Merge Cells.
Wrapping Up
This article showed you four ways to combine multiple cells into one separated by comma in excel. We hope you got what you came for. The practice file contains examples for all those methods including the visual basic code. Feel free to use it, and leave some comments below.
