Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by PuffyGrl82 View Post
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.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #3   Report Post  
Member
 
Posts: 31
Default

Quote:
Originally Posted by PuffyGrl82 View Post
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
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.
  #4   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by Mazzaropi View Post
-------------------------------------------------------------------------


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.
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.
Attached Images
  
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autosum column values, if separate column values equal certain val Hulqscout Excel Worksheet Functions 1 November 5th 08 07:37 PM
Counting Unique Values That Are Separate by Commas in a Column Rothman Excel Discussion (Misc queries) 2 March 25th 08 09:06 PM
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets Doctorjones_md Excel Discussion (Misc queries) 7 June 8th 07 09:32 PM
find a cell matching separate column and row values LQEngineer Excel Worksheet Functions 2 July 26th 06 07:10 AM
Adding Values Based on a Separate Column binder Excel Discussion (Misc queries) 2 February 14th 05 08:17 PM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"