-31

If I have different values in different cells, how can I join them together with a function with a delimiter of my own choosing (like "," or "| ", etc.).

For example:

So if you have:

A1: foo
A2: bar
A3: baz

You can type in A4:

=somefunction("",A1:A3)

And you will get in A4: 

foo bar baz

Moreover, what if the inputs are results of an array function, like: {foo, bar, bar}

Maybe a UDF would work?

I know in Microsoft Office 2016 there is the textjoin function, but it is only available for Office 365 subscribers. And this function cannot handle array inputs.

Freelensia
  • 402
  • 6
  • 18
  • 3
    There was code posted for this earlier today. https://stackoverflow.com/questions/50741018/concatenate-unknown-number-of-column-and-rows/50741445?noredirect=1#comment88534642_50741445 It is also a fairly frequently asked question. – QHarr Jun 08 '18 at 15:35
  • 9
    This question got discussed on Meta, explaining the downvotes because of the original link to freelensia.com, see: https://meta.stackoverflow.com/questions/369226/could-this-post-be-hiding-spam-advertising-in-plain-sight – Cœur Jun 09 '18 at 01:36

1 Answers1

11

Try this user defined function. It is quite versatile. It will take for input hard-coded strings, single cell, cell ranges, arrays, or any mixture of them. Blanks will be ignored. See the photo for outputs.

Public Function TJoin(Sep As String, ParamArray TxtRng() As Variant) As String
On Error Resume Next
'Sep is the separator, set to "" if you don't want any separator. Separator must be string or single cell, not cell range
'TxtRng is the content you want to join. TxtRng can be string, single cell, cell range or array returned from an array function. Empty content will be ignored
Dim OutStr As String 'the output string
Dim i, j, k, l As Integer 'counters
Dim FinArr(), element As Variant 'the final array and a temporary element when transfering between the two arrays

'Go through each item of TxtRng(),  depending on the item type, transform and put it into FinArray()
i = 0 'the counter for TxtRng
j = 0 'the counter for FinArr
k = 0: l = 0 'the counters for the case of array from Excel array formula
Do While i < UBound(TxtRng) + 1
    If TypeName(TxtRng(i)) = "String" Then 'specified string like "t"
        ReDim Preserve FinArr(0 To j)
        FinArr(j) = "blah"
        FinArr(j) = TxtRng(i)
        j = j + 1
    ElseIf TypeName(TxtRng(i)) = "Range" Then 'single cell or range of cell like A1, A1:A2
        For Each element In TxtRng(i)
            ReDim Preserve FinArr(0 To j)
            FinArr(j) = element
            j = j + 1
        Next
    ElseIf TypeName(TxtRng(i)) = "Variant()" Then 'array returned from an Excel array formula
         For k = LBound(TxtRng(0), 1) To UBound(TxtRng(0), 1)
            For l = LBound(TxtRng(0), 2) To UBound(TxtRng(0), 2)
                ReDim Preserve FinArr(0 To j)
                FinArr(j) = TxtRng(0)(k, l)
                j = j + 1
            Next
         Next
    Else
        TJoin = CVErr(xlErrValue)
        Exit Function
    End If
i = i + 1
Loop

'Put each element of the new array into the join string
For i = LBound(FinArr) To UBound(FinArr)
    If FinArr(i) <> "" Then 'Remove this line if you want to include empty strings
    OutStr = OutStr & FinArr(i) & Sep
    End If
Next
 TJoin = Left(OutStr, Len(OutStr) - Len(Sep)) 'remove the ending separator

End Function

Screenshot:
Screenshot

Let's say your cells look like this:

  A                          B
1 find                       good
2 apples                     for free
3 online                     now
4 at                         from this site:
5 https://www.example.com

You can put in some formulas like:

=tjoin(" ","please",$A$1,$A$3:$A$5)
=tjoin($A$6,$A$1:$A$5,"C1")
=tjoin(" ",IF(LEN($A$1:$A$5)>3,$A$1:$A$5,""))
=tjoin(" ",IF(LEN($A$1:$B$5)>3,$A$1:$B$5,""))

Your results will be:

please find online at https://www.example.com
find -- apples -- online -- at -- https://www.example.com -- C1
find apples online at https://www.example.com
find good apples for free online from this site: https://www.example.com
Lun
  • 428
  • 4
  • 20