Excel function needed to conditionally concatenate cells

Posted on 2011-10-21
Last Modified: 2012-05-12

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!
Question by:SStroz
    LVL 80

    Accepted Solution

    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)
    LVL 17

    Expert Comment

    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.

    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
        JoinUp = result
    End Function

    Open in new window

    LVL 50

    Expert Comment

    by:barry houdini
    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
    LVL 7

    Author Closing Comment


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

    Thanks again!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now