Is there a way in Excel to take a column and convert it to a concatenated string

Try using TRANSPOSE function and pressing F9 on it:

+---+--------+
|   |   A    |
+---+--------+
| 1 | Joe    |
| 2 | Bob    |
| 3 | George |
+---+--------+

B1: (type in function bar)

=TRANSPOSE(A1:A3)

Highlight TRANSPOSE(A1:A3), and press F9.

It will give you:

{"Joe","Bob","George"}

Copy and paste that list. That's it.

To concatenate the words, you just have to do:

=CONCATENATE(TRANSPOSE(A1:A3))

Highlight TRANSPOSE(A1:A3), press F9, then remove the brackets:

=CONCATENATE("Joe","Bob","George")

There are two basic ways that I know of.

Quickest way -

  1. Column A lists your names
  2. Copy the value of A1 to B1 - "Joe"
  3. In B2, enter the formula `=B1 & ", " & A2`
  4. Select B2, Copy the formula, and paste down the rest of column B the entire length of column A. The last cell in column B will contain a comma separated list of values from column A.

Better way -
Make your own vb function that iterates across a range of cells, and instead of summing them like sum, just concatenate them.

I do this all the time, so if anyone knows of an actual built-in Excel function buried somewhere in MS documentation that concatenates ranges, you will save the day.


Add this as a macro to the Excel sheet and use it as a custom function with range and delimiter as input

Function Concat(myRange As Range, Optional myDelimiter As String) As String
  Dim r As Range
  Application.Volatile
  For Each r In myRange
    If Len(r.Text) Then
      Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text
    End If
  Next
End Function