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.
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 & c.Value & sChar2bAdded Next c sOutput = Left(sOutput, Len(sOutput) - Len(sChar2bAdded)) rngTarget = sOutput On Error GoTo 0 Exit Sub ConCatwChar_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConCatwChar" End Sub
Function Concat2(myRange As Range, Optional myDelimiter As String) Dim r As Range Application.Volatile For Each r In myRange If Len(r.Text) > 0 Then Concat2 = Concat2 & r & myDelimiter End If Next r If Len(myDelimiter) > 0 Then Concat2 = Left(Concat2, Len(Concat2) - Len(myDelimiter)) End If End Function
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 or the function, you’ll get a single cell with all the names concatenated, but separated by specified delimiter.