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.
=CONCAT(B5:C11)
- 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:
=CONCATENATE(C4:I4&",")
- 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.
=TEXTJOIN(",",TRUE,B5:B13)
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:
=C4&","&D4&","&E4&","&F4&","&G4&","&H4&","&I4
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
Get FREE Advanced Excel Exercises with Solutions!