How to Concatenate Rows in Excel with Comma (4 Easy Methods)

We’ll use the following example where we have a list of G-7 countries in 7 rows (B5 to B11). We’ll put the entire list in a single cell, with the country names separated by commas.

Method 1 – Utilize the CONCAT Function to Concatenate Rows

Steps:

• Insert another column containing delimiter commas.

• We have inserted the comma ”,” in each cell from C5 to C10 but kept the cell C11 empty because we don’t want a comma at the end of the joint text.
• In cell F7, insert the following formula.

• Press the Enter key, and you will see your desired result.

Method 2 – Concatenate Rows by Applying the CONCATENATE Function

Steps:

• Rearrange the data into a single row since this function doesn’t work properly when data is in multiple rows.

• In the B7 cell, use the following formula:

• Select the portion C4:I4&”,” in the formula and press F9. It will transform the cell reference into cell content.

• Eliminate the braces {} from both ends.

• Press Enter. You will get your desired result.

Method 3 – Utilize the TEXTJOIN Function to Concatenate Rows

We’ve inserted blank values in the dataset, which need to be ignored.

Steps:

• Enter the formula below in cell D7.

The TEXTJOIN function uses the comma argument as a delimiter, with the second argument TRUE allowing it to ignore empty cells.

• Hit Enter.

Method 4 – Concatenate Rows Using the Ampersand Operator

Steps:

• Go to cell B7 and insert an equals sign (=) in it.

• Select cell C4 and write &“,”.

• Insert a comma, select D4, and write &“,”.

• Repeat for all cells.
• Here is the final formula for the example:

Things to Remember

• Try to use the CONCAT function instead of CONCATENATE function as it is becoming obsolete.
• Do not use the Ampersand Operator if your data set is very large.
• All the methods described here except the CONCATENATE function are applicable to both row-wise and column-wise joining

Download the Practice Workbook

<< Go Back to Range | Concatenate | Learn Excel