Joining many text strings in excel is not easy. The Concatenate function accepts only a single cell reference in each argument.

Example,

=Concatenate(A1, A2, A3) + ENTER

To quickly select cells you can press CTRL and click on cells you want to be included in the concatenate function.

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 would be tedious to add, clicking on each cell.

Concatenate a cell range

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

Let´s concatenate these values:

  1. Select cell B1.
  2. Type =A1:A50 in formula bar.
    Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50)
  3. Press F9.
  4. Delete curly brackets in formula bar.
  5. Type =Concatenate( in front of all characters in formula bar.
  6. Type ) at the end in formula bar.
  7. Press Enter

Formula in cell B1:

=CONCATENATE("A1","B1","C1","D1","E1","F1", ...) + ENTER

Create delimiting character

It would be great if we could have a character separating values. Here is how:

  1. Select cell B2.
  2. Type =A1:A50&", " in formula bar.
    Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50)&", "
  3. Press F9.
  4. Delete curly brackets in formula bar.
  5. Delete last delimiting character.
  6. Type =Concatenate( in front of all characters in formula bar.
  7. Type ) after last character in formula bar.
  8. Press Enter

Formula in cell B2:

=CONCATENATE("A1, ","B1, ","C1, ","D1, ","E1, ","F1, ", ...) + ENTER