I have a data set of 522 rows in 1 column.

I need to have all this data combined into one "cell" or sheet so that I can copy it to another application as a list comma delimited.

For Example:

Bobby
Adam
Mallary
Alicia
David

needs to show up as:

Bobby, Adam, Mallary, Alicia, David


HELP!

10/22/2007 2:47:39 PM

good lord...not sure what higher level function there might be, but concatenate sounds like the starting point

=CONCATENATE(A7,", ",A8,", ",A9)

but that's going to take forever

of course, you could cascade the concatenates so that you group say 10 of them, then copy and past the formula to concatenate the next 10...then you can concatenate those into bigger groupings, etc

[Edited on October 22, 2007 at 3:04 PM. Reason : .]

10/22/2007 3:03:00 PM

paging LimpyNuts

10/22/2007 3:11:03 PM

file save as - set type to Text (tab delimited) - open the file in something like UltraEdit or wordpad - highlight the tab - do a replace - replace with a comma

that's what i would do - it'd take less than 2 minutes

10/22/2007 3:28:02 PM

assuming all of your names are in column A

copy the first name to cell B1

in B2 use the following formula: =CONCATENATE(B1,",",A2)

drag it down to the end of your list

profit


^didn't work for me. nor did saying it as a CSV. both approaches resulted in a column, not a list

10/22/2007 3:30:41 PM

Copy, paste special, transpose

Save as DOS csv

[Edited on October 22, 2007 at 3:43 PM. Reason : Bobby,Adam,Mallary,Alicia,David]

10/22/2007 3:43:09 PM

Quote :

"Copy, paste special, transpose Save as DOS csv"

10/22/2007 4:15:03 PM

i like my method better
dont have to save, open as text etc
its all right in the worksheet youre working in

but now i know what that transpose does, so its all good.

10/22/2007 5:33:45 PM

Synapse's method is probably easiest. Here's a macro that will do it:

Public Sub thefunction()

  Dim r As Range, c As Range, t$
  
  Set r = Selection
  If r.Areas.Count = 1 Then
   For Each c In r.Cells
    t = t & c.Text & ","
   Next
  End If
  
  r.Offset(0, 1).Cells(1, 1).Formula = t

End Sub

10/22/2007 7:38:39 PM

^^ Yea your method was fine, I just rather like being able to ctrl-[shortcut] with my left hand and operate the mouse with my right and not have to worry about typing.

10/22/2007 9:20:27 PM

you can bind macros to CTRL+[whatever], that's why i like them.

10/22/2007 11:18:06 PM

yeah i love those programmed macros in excel. REAL time savers.

10/22/2007 11:37:05 PM

^^ That would be great once I have the macro written, but for this thread, I could have done all the work without actually typing. If this was something I had to do over and over and over again, I would create a macro for sure.

10/23/2007 12:24:56 PM

copy, paste special , transpose

10/23/2007 6:11:32 PM