COMBINE ALL VALUES IN COLUMN INTO ONE CELL

[chasethewind]

I cannot figure this out. In column A

100
1001
324
542
562

And would like to place in B1

100, 1001, 324, 542, 562

Any help would be appriciated.

 

[SkipVought]

Hi,

Paste this code into a MODULE in the VB Editor. alt+F11 toggles between the active sheet and the VB Editor. Insert a Module in the VB Editor. Paste this code into a MODULE in the VB Editor.

Function MakeList(rng As Range, Optional TK As String = "'", Optional CM As String = ",") As String
'SkipVought/2005 Jun 13
'--------------------------------------------------
' Access: N/A
'--------------------------------------------------
':this function returns a single-quoted list that can be used, for instance _
in an IN Clause in SQL _
"WHERE PART_ID IN (" & MakeList([SomeRange]) & ")"
'--------------------------------------------------
    Dim r As Range
    
    For Each r In rng
        With r
            MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Use this function like you would any other spreadsheet function. Reference the RANGE that you want in the one cell, with values separated by commas, like this...
[tt]
=MakeList(Yourrange,"")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Use Skip's solution, or download Laurent Longre's free MOREFUNC add-in, which has many useful functions, one of which is called MCONCAT, which does the same as Skip's function. See here:

http://xcell05.free.fr/morefunc/english/#Download

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[chasethewind]

Great. Looks like it is going to work.

Thank you.

 

[JPaules]

Is there a reason you didn't want to use the CONCATENATE function?

--
JP

 

[chasethewind]

I only leared about the function the other day. Can you use this function with a range? Like A1 through A200? I can easily use it with a few cells, but not 200.

=Concatenate(A1:A200) did not work for me.

 

[JPaules]

I assume you are using Excel 2003 or older. Excel 2007 allows you to have 255 arguments. You could do it in multiple steps but it looks like some sort of code would be faster.

--
JP

 

[chasethewind]

Excel 2007

 

[lionelhill]

If you want to avoid VB, you can do this.

Place in B1:
=IF(A2<>"", A1&", "&B2, A1)

Copy to the end of the column.
B1 will now contain the text you want. You can add to the list in A and the contents of B1 will remain correct. Cells B2 onwards will contain partial versions of your list, and will end with a line of zeroes corresponding to empty cells in A; sorry about these side-effects!