Excel - merging contents in multiple cells

[king1000]

I have a column of 80 cells. Most of the cells contain text but some are blank. I would like to merge the contents of these 80 cells into one cell. The text from each cell needs to seperated by a semi-colon in the merged cell. I would also like to ignore the blank cells. I have looked at the contantonate function but it would be very tedious to use in this case. Any helpful tips would be much appreciated.

 

[SkipVought]

Hi,

I just happen to have a user defined function that I often use to do exactly that.

Paste this into a MODULE in your VB Editor (alt+F11). If you have no MODULE in your workbook, Insert > Module

The first argument is a range, which would be your 80 cell range. The next 2 arguments are optional. They control the 1) delimiters around each element and 2) the delimiters between each element. Use this function as you would any other in a cell. Your cell formula might look something like this...
[tt]
=MakeList(A1:A80,"",";")
[/tt]

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

Skip,

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

 

[king1000]

Many thanks for your assistance! The only remaining issue is how to deal with blanck cells. At the moment if I have a coumn with a few blank cells, the formula with inset a semi colon for each blank cell in the final list. Is there any way to just have the semi colons added between text in the populated cells?

Thanks again!

 

[SkipVought]

Filter out the BLANKS.

Use this function instead...

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[b].SpecialCells(xlcelltypevisible)[/b]
        With r
            MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Skip,

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