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
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