Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Excel function needed to conditionally concatenate cells


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!
1 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)
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

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
SStrozAuthor Commented:

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

Thanks again!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now