How to combine multiple columns if they are not empty
I want to concatenate the values of 9 columns into 1 column with | between values. The problem is that some of the columns are empty for some rows, which makes it pretty ugly to use the = CONCATENATE () function, since you would need to check = if (A2 = ""; ...) for each of the 9 columns.
Is there a smarter way to combine these multiple columns in excel just using the cell that has the values? Maybe using VBA?
To illustrate this, the sheet looks something like this:
| A | B | C | D | E | F | G | H | I |
|------+------+---+-------+---------+---+-----+-----+-----|
| lion | king | | | animals | | | | dog |
| lion | | | queen | | | cat | jet | |
The output for line 1. should be: "Lion | king | animals | dog" and for line 2. "Lion | queen | cat | jet"
Can anyone please help?
Many thanks!
source to share
You can 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 the standard code module and use it in your sheet like so:
=MyConcat(A1:J1)
Not really how it is done with a worksheet formula without using the useless SUBSTITUTE / IF functions.
EDIT (OP's 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
source to share
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
source to share
You can use UDF like this (adapt to your 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
source to share