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

Question

I want to take a list of names that are in multiple rows and combined them into a single cell like this:

Joe
Bob
George

and convert that into one cell that has this:

"Joe", "Bob", "George"

Answer 1

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")

Answer 2

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.