Joining many many cell values in excel is not easy. The Concatenate function allows you to only reference a single cell in each argument.

Example,

Formula in cell D2:

=Concatenate(A1, A2, A3,B1,B2,B3)

To quickly select cells you can press and hold CTRL then click on cells you want to be included in the concatenate function. The CTRL key saves you a little bit of time but not much.

Example,

  1. Select a cell
  2. Type =concatenate( in formula bar
  3. Press and hold CTRL button and click cells to be included.
  4. Release CTRL button
  5. Type ) in formula bar and press Enter

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, clicking on each cell.

Tip 1! The solutions below 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.

TEXTJOIN function allows you to easily concatenate values:

TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Textjoin

Tip 2! 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.

Table of Contents

  1. Concatenate cell values in a row into a single cell
  2. Concatenate cell values in a column into a single cell
  3. Concatenate a multi-column and multi-row cell range
  4. Create delimiting character

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.

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

Here is how to concatenate these values:

  1. Double click 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:

Concatenate cell values based on a condition [No VBA]

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

Comments(0) Filed in category: Concatenate, Excel

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:

Instructions

Here is a larger range, A1:A50 with some numbers.

Let us concatenate these numbers:

  1. Double click 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 formula bar
  6. Type an ending parentheses ) at the very end
    =CONCATENATE(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 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.

Recommended article:

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 […]

Comments(249) Filed in category: Concatenate, Excel, Textjoin

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 A1:D6.

  1. Double click  on cell B8
  2. Type an equal sign = and then select cell range A1:D6 with your mouse
  3. Press F9 to convert cell reference to values
  4. Delete equal sign, then press Enter
  5. Select cell B8
  6. Press CTRL + H to search and replace values, search for ; and replace with ,
  7. Click "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:

Concatenate unique distinct values

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]

Comments(2) Filed in category: Concatenate, Excel, Textjoin

Create delimiting character

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

Here is how:

  1. Double click  B8.
  2. Type =A1:D6&"|"
  3. Press function key F9 to convert cell reference to values:
    {"B7|","Y8|","I9|","S9|"; "Y6|","J8|","S7|","F7|"; "R4|","V8|","S7|","H6|"; "I8|","U2|","Z5|","W4|"; "C7|","S4|","N2|","A3|"; "U7|","Q1|","B1|","L4|"}
  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 comma, click "Replace" button. Not "Replace All", this will replace 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