Take the work out of Excel's CONCATENATE Formula


Excel's CONCATENATE formula is widely used to combine the contents of multiple cells. However when dealing with a long range of cells, you need to spend quite a bit of time keying in the cells references and connectors. If you want to combine the contents of cells A1 through P1 it's necessary to enter:

=CONCATENATE(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&P1)

On a busy day, tedious little tasks like this will keep you in the office far longer into the night than you want to be. Excel does not allow a range of cells to be selected like this:

=CONCATENATE(A1:P1)

. . . this will just result in #VALUE! error.

Pearson Software Consulting has posted a Visual Basic code to its site which will allow this shortcoming to be overcome. See: http://www.cpearson.com/excel/stringconcatenation.aspx Press ALT + F11 and in Visual Basic right click on VBA project and insert the below code in new module. Save and close. Now you can simply type in:

=StringConcat(" ",A1:P1)

. . . and get the same result as the CONCATENATE formula which references each cell in the specificed range.

Function StringConcat(Sep As String, ParamArray Args()) As Variant

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' StringConcat

' By Chip Pearson, chip@cpearson.com, www.cpearson.com

' www.cpearson.com/Excel/stringconcatenation.aspx

' This function concatenates all the elements in the Args array,

' delimited by the Sep character, into a single string. This function

' can be used in an array formula. There is a VBA imposed limit that

' a string in a passed in array (e.g., calling this function from

' an array formula in a worksheet cell) must be less than 256 characters.

' See the comments at STRING TOO LONG HANDLING for details.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim S As String

Dim N As Long

Dim M As Long

Dim R As Range

Dim NumDims As Long

Dim LB As Long

Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''

' If no parameters were passed in, return

' vbNullString.

'''''''''''''''''''''''''''''''''''''''''''

If UBound(Args) - LBound(Args) + 1 = 0 Then

StringConcat = vbNullString

Exit Function

End If

For N = LBound(Args) To UBound(Args)

''''''''''''''''''''''''''''''''''''''''''''''''

' Loop through the Args

''''''''''''''''''''''''''''''''''''''''''''''''

If IsObject(Args(N)) = True Then

'''''''''''''''''''''''''''''''''''''

' OBJECT

' If we have an object, ensure it

' it a Range. The Range object

' is the only type of object we'll

' work with. Anything else causes

' a #VALUE error.

''''''''''''''''''''''''''''''''''''

If TypeOf Args(N) Is Excel.Range Then

'''''''''''''''''''''''''''''''''''''''''

' If it is a Range, loop through the

' cells and create append the elements

' to the string S.

'''''''''''''''''''''''''''''''''''''''''

For Each R In Args(N).Cells

If Len(R.Text) > 0 Then

S = S & R.Text & Sep