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