How to Concatenate multiple columns if not empty

Question

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

Answer 1

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