ExcelBanter - Formula to separate cell values in a column with commas

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Formula to separate cell values in a column with commas (https://www.excelbanter.com/excel-worksheet-functions/270035-formula-separate-cell-values-column-commas.html)

Formula to separate cell values in a column with commas

Dear ExcelBanter community,

I have been stuck trying to figure out something I'm sure some of you experts would consider simple. I have an Excel worksheet where column C has the first names of a list of company employees. As an example, we may have cell C1 = "Debbie", C2 = "James", C3 = "Tony"...

What I need to do is figure out an equation where I can somehow get all these first names separated by a comma and a space. It would be great if I could get cell D1 = "Debbie, James, Tony, ...".

Any help would be appreciated. I've been trying to figure this out for the past week to no avail. Thank you!

- Vivian

Quote:

Originally Posted by PuffyGrl82 (Post 964346)

Dear ExcelBanter community,

I have been stuck trying to figure out something I'm sure some of you experts would consider simple. I have an Excel worksheet where column C has the first names of a list of company employees. As an example, we may have cell C1 = "Debbie", C2 = "James", C3 = "Tony"...
What I need to do is figure out an equation where I can somehow get all these first names separated by a comma and a space. It would be great if I could get cell D1 = "Debbie, James, Tony, ...".
Any help would be appreciated. I've been trying to figure this out for the past week to no avail. Thank you!
- Vivian

-------------------------------------------------------------------------

Dear Vivian, Good Evening.

You can do this using formulas or VBA Programming.

1) Fórmula

=CONCATENATE(C1,", ",C2,", ",C3,", ",C4,", ",C5,", ",C6,", ",C7,", ",C8,", ",C9,", ",C10,", ")

If you have a very big column of data this is not a soft solution.
It will be a hard work solution.

About VBA Programming I´m not an expert on it.

Test it and tell me if this worked for you.

Quote:

Originally Posted by PuffyGrl82 (Post 964346)

Other than the CONCATENATE, you could also create a very simple Function.

To do this, go into VisualBasic and create a new Module. Copy and Paste in the following code:

Function JoinUp(CellRange)
For Each c In CellRange
NameList = NameList & c.Value & ", "
Next
NameList = Left(NameList, Len(NameList) - 2)
JoinUp = NameList
End Function

Back in your worksheet, type in the formula:
=JoinUp(C1:C3)

You can use this Function on any range of cells (in Rows or Columns) to join values into a comma-separated list.

Quote:

Originally Posted by Mazzaropi (Post 964348)

You may be interested in a similar formula...
My data is in column A.
I am using Column C to put in the Concatenate formula.
Starting on row 2...
=CONCATENATE(A2,", ",A3)
Then on row 3:
=CONCATENATE(A4,",",C2)
Then I dragged down to the 2nd to last row (since the formula is looking one row down). This is just adding on one more item to the list with each successive row you copy the formula down to. See the attached images.