Concatenate a Range

Concatenate a Range

Use this function to easily concatenate a selected range of cells. You can either add a delimiter or not depending how you use the formula. Here’s how you use the formula function:

Fill in the cells A1 to D2 as shown below.

In cell E1 type the following formula =CONCAT(C8:E10) to concatenate selected contiguous range of cell

In cell E2 type the following formula =CONCAT(C8:E10,”|”) Concatenate range with delimiter

A B C D E
1 aa bb cc dd aa|bb|cc|dd
2 aa bb cc dd aabbccdd

[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Concatenate Range”]
Public Function Concat(MyRange As Range, Optional myDelimiter As String)

Dim r As Range

Application.Volatile
For Each r In MyRange
Concat = Concat & r & myDelimiter
Next r
If Len(myDelimiter) > 0 Then
Concat = Left(Concat, Len(Concat) – Len(myDelimiter))
End If
End Function
[/vb]