4

I want to concatenate the values of 9 columns into 1 column with | between the values. The problem is that some of the columns are empty for some of the rows, making it pretty ugly to use the =CONCATENATE() function as you would need to check =if(A2="";...) for every of the 9 columns.

Is there a smarter way to combine these multiple columns in excel, only using the cell that have values in it? Maybe using VBA?

To exemplify, the sheet looks something like:

| A    | B    | C | D     | E       | F | G   | H   | I   |
|------+------+---+-------+---------+---+-----+-----+-----|
| lion | king |   |       | animals |   |     |     | dog |
| lion |      |   | queen |         |   | cat | jet |     |

Output for the 1. line should be: "lion|king|animals|dog" and for the 2. line: "lion|queen|cat|jet"

Can someone help?

Thanks a lot upfront!!

Daniel Dušek
  • 13,683
  • 5
  • 36
  • 51
user1507035
  • 137
  • 1
  • 4
  • 13
  • unless there is some other data that you are not showing, an empty cell would not affect the concatenate function – SeanC Dec 29 '14 at 13:57
  • @SeanCheshire The empty cells would not affect the function, but used in the way the OP suggests, would still produce a delimiter - resulting in multiple unwanted delimiters. Maybe the OP could update the question to clarify this. – SierraOscar Dec 29 '14 at 13:59

3 Answers3

5

You could use a simple UDF:

Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "|"): Next
  MyConcat = Left(xx, Len(xx) - 1)
End Function

Copy the above code into a standard code module, and use in your worksheet like so:

=MyConcat(A1:J1)

There isn't really anyway of doing this with a worksheet formula without using messy SUBSTITUTE/IF functions.

EDIT (OP request)

To remove duplicates:

Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "" Or InStr(1, xx, x & "|") > 0, xx & "", xx & x & "|"): Next
  MyConcat = Left(xx, Len(xx) - 1)
End Function
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • just out of interest: could you also modify it to make sure no duplicates are included in the Concat? If 2 columns have the exact same value, that it only appears once in the string? – user1507035 Dec 29 '14 at 14:52
  • I need your expert opinion. In may case this solution work fine except one problem. The problem is if all cell value is null then the function output is #value. I am struggling to solve this. how can i solve this – karim_fci Feb 14 '17 at 16:19
  • Hi @karim_fci please create a new post for your question and if needed you can add a link to this question to help explain the background. – SierraOscar Feb 14 '17 at 16:33
3
Public Function ConcatItNoDuplicities(ByVal cellsToConcat As Range) As String
    ConcatItNoDuplicities = ""
    If cellsToConcat Is Nothing Then Exit Function
    Dim oneCell As Range
    Dim result As String
    For Each oneCell In cellsToConcat.Cells
        Dim cellValue As String
        cellValue = Trim(oneCell.value)
        If cellValue <> "" Then
            If InStr(1, result, cellValue, vbTextCompare) = 0 Then _
                result = result & cellValue & "|"
        End If
    Next oneCell
    If Len(result) > 0 Then _
        result = Left(result, Len(result) - 1)
    ConcatItNoDuplicities = result
End Function

enter image description here

Daniel Dušek
  • 13,683
  • 5
  • 36
  • 51
0

You could use a UDF like this (adjust to own needs):

Function Conc(v As Variant, Optional ByVal sDelim As String = "") As String
    Dim vLoop As Variant
    If IsArray(v) Or TypeName(v) = "Range" Then
        For Each vLoop In v
            If Conc = "" Then
                Conc = vLoop
            ElseIf vLoop <> "" Then
                Conc = Conc & sDelim & vLoop
            End If
        Next vLoop
    Else
        Conc = CStr(v)
    End If
End Function
xificurC
  • 1,168
  • 1
  • 9
  • 17