Excel function needed to conditionally concatenate cells

Gurus,

I have a worksheet with a range of columns (B - G) that may or may not have data in them.  I need to seperate each cell that contains data with a semicolon (;) but ONLY if they contain data.

example output:

Column B; Column C
Column B; Column C; Column D
Column D
Column B; Column D
Column B
Column B; Column C; Column H

Note that some rows only have one item where other rows have multiple columns.  Each data item must be seperated by a semicolon - but not at the end.

I'd prefer a formula instead of a macro but any help is appreciated.  

Thanks in advance!
LVL 7
SStrozAsked:
Who is Participating?
 
byundtCommented:
If there are less than 99 characters, you might use a brute force formula like:
=MID(IF(B2="","","; " & B2) & IF(C2="","","; " & C2) & IF(D2="","","; " & D2) & IF(E2="","","; " & E2) & IF(F2="","","; " & F2) & IF(G2="","","; " & G2) & IF(H2="","","; " & H2),3,99)
0
 
andrewssd3Commented:
I know you did not want code, but I have used this user-defined function frequently.  If you add it to a module in your spreadsheet, then use it as a function, e.g.
=JOINUP(A1:H1,";")

Open in new window

I think it will do what you want.
Public Function JoinUp(ByRef arrayToJoin As Object, _
        Optional ByVal delimiter As String = "|") As Variant
    
    Dim rng As Excel.Range
    Dim c As Excel.Range
    Dim result As Variant
    result = CVErr(xlErrValue)
    
    If TypeName(arrayToJoin) <> "Range" Then GoTo Exit_Function

    Set rng = arrayToJoin
    
    result = vbNullString
    For Each c In rng.Cells
        If Not IsEmpty(c) Then
            result = result & CStr(c.Value) & delimiter
        End If
    Next c
    
    If Len(result) > Len(delimiter) Then
        result = Left$(result, Len(result) - Len(delimiter))
    End If

Exit_Function:
    JoinUp = result
End Function

Open in new window

0
 
barry houdiniCommented:
One option is to download MOREFUNC add-in and use MCONCAT function, i.e. in a formula like this

=SUBSTITUTE(MCONCAT(IF(B2:G2<>"","; "&B2:G2,"")),"; ","",1)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
SStrozAuthor Commented:
All,

Thanks for all of the other suggestions but byundt's did the trick.

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.