Concatenate with Separators
Ever needed the values from a range of cells as a string separated by commas ? I used do this in a two step concatenate : the first step is for every cell in the range and then another concatenate for all the “concatenated cells”.
So today, I decided to create my own UDF to generate a string from a selection of cells and any “separator”.
Here’s the ConcSep function :
Public Function ConcSep(InCells As Range, Sep As String) As String
Dim OutStr As String
Dim Cell As Variant
OutStr = “”
If InCells Is Nothing Then Exit Function
On Error Resume Next
For Each Cell In InCells
OutStr = OutStr & Cell.Value & Sep
Next Cell
OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
ConcSep = OutStr
End Function
And here’s the output :
Obviously, this now goes into the add-in you created . and will also be part of my Utilities project.
