Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
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. |
#4
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autosum column values, if separate column values equal certain val | Excel Worksheet Functions | |||
Counting Unique Values That Are Separate by Commas in a Column | Excel Discussion (Misc queries) | |||
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets | Excel Discussion (Misc queries) | |||
find a cell matching separate column and row values | Excel Worksheet Functions | |||
Adding Values Based on a Separate Column | Excel Discussion (Misc queries) |