UDF: Concatenate Quickly

The existing Concatenate function within Excel is very useful, however it leaves room for improvements to be made from an efficiency perspective.

The following functions allows a user to select a ranges of cells that are to be concatenated together.

It also allows you to optionally specify a specific delimiter if you do not want to use the default comma (,) delimiter. This allows the user to quickly decide on what range they want to concatenate, and how they should be concatenated using two simple inputs, instead of one input per cell on the standard concatenate.

Single Contiguous Range


 Function Concat(rCon As Range, Optional sD As String = ",")
 
 Application.Volatile
 
 Dim c As Range
 Dim strCon As String
 
 'QUIT IF NO RANGE PROVIDED
 If rCon Is Nothing Then
 Concat = "#NoRangeSelected"
 Exit Function
 End If
 
 'ITERATE THROUGH EACH SELECTED ITEM
 For Each c In rCon
 
 'IGNORE ANY BLANK VALUES IN THE RANGE
 If c.Value <> "" Then
 
 'IF THIS IS THE FIRST ITEM, DO NOT ADD PRECREEDING DELIMITOR
 If strCon = "" Then
 strCon = c.Value
 Else
 strCon = strCon & sD & c.Value
 End If
 
 End If
 
 Next
 
 'OUTPUT
 Concat = strCon
 
 End Function

Thanks to contributions from: pmo86

Non-Contiguous Range

For multiple ranges, where the ranges are not contiguous, the following piece of code allows those ranges to be concatenated together – thanks to contributor: xlViki


 Function ConcatNC(Sep As String, ParamArray RngList() As Variant) As String
 
 Dim Rng As Variant
 Dim cell As Range
 Dim x As Variant
 
 For Each Rng In RngList
 
 If TypeOf Rng Is Range Then
 For Each cell In Rng.Cells
 ConcatNC = ConcatNC & cell.Value & Sep
 Next cell
 ElseIf IsArray(Rng) Then
 For Each x In Rng
 ConcatNC = ConcatNC & x & Sep
 Next x
 Else
 ConcatNC = ConcatNC & Rng & Sep
 End If
 
 Next Rng
 
 ConcatNC = Left(ConcatNC, Len(ConcatNC) - Len(Sep))
 
 End Function