Concatenate cell values

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a single cell in each argument.

Formula in cell B6:

Press and hold CTRL to quickly select cells you want to concatenate then press with left mouse button on cells you want to be included in the concatenate function.

The CTRL key saves you a little bit of time but not much if you have many cells to join.

The above example works if you don't have so many cells to add. A large range like A1:Z100 would be very tedious to add, press with left mouse button oning on each cell.

What can I do to speed things up? It depends on the Excel version you are using.

What options do I have?

The solutions presented in this article are for earlier versions than Excel 2016 however, if you have Excel 2016 you can now finally use the TEXTJOIN function.

It allows you to select an entire cell range and concatenate the values easily, without the need to select each cell as an argument.

The CONCATENATE function has been replaced with the CONCAT function in Excel 2016, it allows you to combine cells from an entire cell range.

The difference between the TEXTJOIN and CONCATENATE function is that you may specify a delimiting character in the TEXTJOIN function and ignore blank cells.

If you often concatenate values and don't have Excel 2016 I recommend using a user defined function to concatenate values. It works just like the TEXTJOIN function.

1. Concatenate cell values in a row

This video demonstrates how to easily concatenate values in a single-row cell range into one cell. You will be surprised how easy it is to join cell values.

Here are the instructions for those of you that don't want to watch the video. The following picture shows you 4 values in row 1.

I know, it is a small number of values but the idea here is to show you the technique.

It is easier to demonstrate with a small number of values for obvious reasons.

Here is how to concatenate these values:

1. Double press with left mouse button on cell C4
2. Select cell range A1:D1
   
   
3. Press function key F9 to convert the cell reference to values
   
   
4. Delete curly brackets: { and }
   
5. Type CONCATENATE( and an ending parenthesis )
   
6. Press Enter
   

Recommended reading:

Recommended articles

Concatenate cell values based on a condition
Add cell values to a single cell with a condition, no VBA in this article.

Back to top

2. Concatenate cell values in a column into a single cell

This video explains the steps to join cell values in a column into a single cell. It also demonstrates how to use a delimiting character:

Here are the instructions for those of you that don't want to watch the video.

The picture to the right shows you a part of a larger range, A1:A50 containing numbers.

You can't see all the numbers in the picture for obvious reasons.

Let us concatenate these numbers in cell range A1:A50:

1. Double press with left mouse button on cell C2.
2. Type =TRANSPOSE(A1:A50)
3. Press function key F9 to convert cell range to values.
4. Delete curly brackets and equal sign.
5. Type =CONCATENATE( in front of all characters in the formula bar.
6. Type an ending parentheses ) at the very end
   =CONCATENATE(1, 2, ... , 50)
7. Press Enter.

Here is an image of all concatenated numbers:

The TRANSPOSE function changes the cell reference from a vertical cell range to a horizontal cell range.

A vertical cell range has a semicolon as a delimiting character and that won't work with the CONCATENATE function.

A horizontal cell range has a comma as a delimiting character, the CONCATENATE function lets you enter arguments with a comma delimiter so this works fine.

It really depends on your regional settings, some countries use the semicolon as a delimiting character between arguments in an Excel function.

Recommended article:

Recommended articles

Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Back to top

3. Concatenate values in a cell range into one cell

This video shows you how to join values in a multi-column and multi-row cell range into one cell:

The following picture shows you values in cell range B2:E5.

1. Double press with left mouse button on cell B7.
2. Type an equal sign = and then select cell range B2:E5 with your mouse.
   
3. Press F9 to convert cell reference to values.
   
4. Delete the equal sign and the curly brackets {}, then press Enter.
5. Select cell B8
6. Press CTRL + H to search and replace values, search for ; and replace with ,
7. Press with left mouse button on "Replace" button.
   
8. Type CONCATENATE( in front of all characters in cell F2 and en ending parentheses after all characters
9. Press Enter

Recommended post:

Recommended articles

Concatenate unique distinct values
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Back to top

4. Create delimiting character

It would be great if we could have a special character separating our values.

Here is how:

1. Double press with left mouse button on cell B8.
2. Type =B2:E5&"|"
   
   
3. Press function key F9 to convert cell reference to values.
   
4. Delete the equal sign and then press Enter.
5. Select cell B8 again.
6. Press CTRL + H to "Search and Replace".
7. Search for a semicolon and replace with a comma, press with left mouse button on "Replace" button.
   Do not press with left mouse button on "Replace All", this will replace values in all cells in your workbook.
   
8. Delete curly brackets and the last delimiting character.
9. Type CONCATENATE( in front of all characters in cell B8 and en ending parentheses after all characters.
10. Press Enter.

Back to top

Concatenate category

Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Excel categories

Leave a Comment