Adding |Pipe| to values in cells

Originally Posted by DonkeyOte

...And yours:

|apple cart|bananas|carrots|||||||||||||||||||||||||||||||||||||||||||||||||

?? My final solution to the OP was:

Function ConCatRange(Rng As Range, Delim As String, Optional Capping As Boolean) As String
'Usage:  =CONCATRANGE(A1:A20, ",")
Dim Cell As Range
Dim sbuf As String
    
    For Each Cell In Rng
        If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Delim
    Next cell
    
    ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))
    If Capping Then ConCatRange = Delim & ConCatRange & Delim
End Function

And from your sample data suggested above, that UDF gives the results:

|Apple Cart|Bananas|Carrots|

I'll have to analyze your suggestion, but I still think your original version works with the amendment I suggested. The only thing I'm trying to learn is how to fill the vData with values when the RngVals is in row format.

Sorry by your suggestion I was referring to your initial revision to my approach!

The latest version should work for blanks (wherever they appear) and cater for strings that contain spaces without need for iteration.

I will put together a version for vector/matrix.

I have little doubt this could be streamlined but I need to get on with some other bits & pieces so in the meantime...

Function CONCATSTR(rngVals As Range, strDelim As String, Optional boolCap As Boolean)
Dim vData, lngCols As Long, lngCol As Long, strTemp As String
lngCols = rngVals.Columns.Count
If Application.CountA(rngVals) = 0 Then CONCATSTR = "Empty": Exit Function
If rngVals.Rows.Count > 1 Then
    If lngCols = 1 Then
        With rngVals
            vData = Application.Transpose(Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))"))
        End With
    Else
        For lngCol = 1 To lngCols Step 1
            If Application.CountA(rngVals.Columns(lngCol)) Then
                With rngVals.Columns(lngCol)
                    vData = Application.Transpose(Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))"))
                End With
                If lngCol < lngCols Then
                    CONCATSTR = CONCATSTR & Replace(Replace(Application.WorksheetFunction.Trim(Join(vData, " ")) & " ", " ", strDelim), "^", " ")
                End If
            End If
        Next lngCol
    End If
Else
    With rngVals
        vData = Evaluate("IF(" & .Address & "<>"""",SUBSTITUTE(" & .Address & ","" "",""^""),REPT(" & .Address & ",1))")
    End With
End If
CONCATSTR = CONCATSTR & Replace(Replace(Application.WorksheetFunction.Trim(Join(vData, " ")), " ", strDelim), "^", " ")
If boolCap Then CONCATSTR = strDelim & CONCATSTR & strDelim
End Function

this should work for both vectors & matrices regardless of blanks etc... for everything except strings that contain valid ^

regards ^: it would pretty straightforward to create a constant array of "unusual chars" and iterate the array until such time as you find a char not present in the range of values being processed and subsequently code that into the replace function.