Concatenate range of cells with a delimiter

Problem

Are you frustrated that you have to concatenate a big range of cells in Excel, but you have to do so using CONCATENATE function by entering each cell and typing a comma after every cell? Well, here’s a solution to it. A procedure or a function whatever you like. If you use the procedure, you choose the input range, delimiter character, and the output range. If you use the function, you enter the optional delimiter character (by default it is a comma), and the input range.

Solution

Procedure:

Public Sub ConCatwChar()
Dim sChar2bAdded As String, rngRng2bCated As Range, sOutput As String, rngTarget As Range, c As Range
On Error GoTo ConCatwChar_Error
'You could use this line to return the concatenated string in this cell
'Set rngTarget = ActiveCell
 
Set rngRng2bCated = Application.InputBox(prompt:="Select the range you’d like to concatenate with a charcter", _
Title:="Select Range", Type:=8)
 
If rngRng2bCated Is Nothing Then Exit Sub
 
'You could use this line to set the default to a comma and remove the inputbox line
'sChar2bAdded = ","
sChar2bAdded = InputBox("Enter the character you’d like to add between other cells", "Enter Character", ",")
 
Set rngTarget = Application.InputBox(prompt:="Select the range you’d like the output", _
Title:="Select Range", Type:=8)
 
For Each c In rngRng2bCated
    sOutput = sOutput &amp; c.Value &amp; sChar2bAdded
Next c
 
sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded))
rngTarget = sOutput
 
On Error GoTo 0
Exit Sub
 
ConCatwChar_Error:
MsgBox "Error " &amp; Err.Number &amp; " (" &amp; Err.Description &amp; ") in procedure ConCatwChar"
End Sub

Function:

Function Concat2(myRange As Range, Optional myDelimiter As String)
    Dim r As Range
 
    Application.Volatile
    For Each r In myRange
        If Len(r.Text) &gt; 0 Then
            Concat2 = Concat2 &amp; r &amp; myDelimiter
        End If
    Next r
    If Len(myDelimiter) &gt; 0 Then
        Concat2 = Left(Concat2, Len(Concat2) - Len(myDelimiter))
    End If
End Function

Example

Let’s say you have range of names in column A as shown in this picture and you want to concatenate (join) this range of cells using a delimiter of quote-comma-quote (‘,’).

After using the procedure to concatenate range of cells

Before concatenate

After using the procedure or the function, you’ll get a single cell with all the names concatenated, but separated by specified delimiter.

After concatenate











About the Author

A co-author of Data Science for Fundraising, an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.