Oh, man, I can’t tell how useful that concatenate function is.
One repetitive use I found is to create OR/AND conditions for Access queries. I copy and paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concat2 function to create a string to use in my Access query.
For example, look at this screen shot:

use of concat function
The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula
=PERSONAL.XLS!concat2(A1:A4,CHAR(34) & " or " & CHAR(34)) |
, and the return string from this function is listed in cell B1.
Now, all I have to do is copy and paste this in Access criteria and put a quotation mark at the beginning and at the end of this string.
I have found one more use of this when I want to store some values in an Array, using the Array function in VBA. In this example, I use a comma, given by the function CHAR(44), instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using the Array function.