FORMATTED Flexible Concatenation Function

**Rick Rothstein** · 09-25-2012, 10:52 PM

This main body of the text in this article is really an exact copy of the article I posted at this link...

Flexible Concatenation Function

because the overall functionality of the two functions is the same, however, the code is different. In the referenced article, the concatentation is performed on the actual underlying value in the cell, not the formatted value displayed in the cell. The code in this article concatenates the values as displayed in the cells themselves. So, to point out the difference, if one of the cells you are concatentating contains this formula in it...

=TODAY()

the cell displays a formatted date value, but the underlying value in the cell is the serial date value; basically, a number of days as an offset number with January 1, 1900 being 1... January 2, 1900 being 2... on up to today (September 25 being the date I posted this message) being 41177. The function in the Flexible Concatenation Function article will concatenate the 41177 value for the TODAY() function whereas the code below will concatenate the formatted date text as shown in the cell. One note with this... if the text for numbers and dates are longer than will fit into the cell, the cell displays a series of # signs across its length... the code below will use those # signs (the exact number displayed in the cell) to form the concatenated value.

Unfortunately, Excel's CONCATENATE function does not work with ranges or arrays (kind of making it worthless in my opinion since simply concatenating the text together using an ampersand produces the same result more compactly). However, the following UDF (user defined function), which can also be used as a function called from other VB code if desired, which I have posted in the past, does work with ranges...

Code:

Function ConCatFormat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
  Dim Index As Long, Rw As Long, Col As Long, Down As Boolean, Rng As Range, Cell As Range
  If IsMissing(Delimiter) Then Delimiter = ""
  Index = LBound(CellRanges)
  Do While Index <= UBound(CellRanges)
    If TypeName(CellRanges(Index)) = "Range" Then
      Set Rng = CellRanges(Index)
      If Index < UBound(CellRanges) Then
        If TypeName(CellRanges(Index + 1)) <> "Range" Then Down = CellRanges(Index + 1) = "|"
      End If
      If Down Then
        For Col = 0 To Rng.Columns.Count - 1
          For Rw = 0 To Rng.Rows.Count - 1
            If Len(Rng(1).Offset(Rw, Col).Value) Then ConCatFormat = ConCatFormat & Delimiter & Rng(1).Offset(Rw, Col).Text
          Next
        Next
        Index = Index + 1
      Else
        For Each Cell In Intersect(Rng, Rng.Parent.UsedRange)
          If Len(Cell.Value) Then ConCatFormat = ConCatFormat & Delimiter & Cell.Text
        Next
      End If
    Else
      If CellRanges(Index) = "||" Then
        ConCatFormat = ConCatFormat & Delimiter & "|"
      Else
        ConCatFormat = ConCatFormat & Delimiter & CellRanges(Index).Text
      End If
    End If
    Index = Index + 1
  Loop
  ConCatFormat = Mid(ConCatFormat, Len(Delimiter) + 1)
End Function

This function allows you to create formulas like this...

=ConCatFormat("-",A1:B3,C1,"HELLO",E1:E2)

and the contents of the indicated cells, and the text constant "HELLO", will be concantenated together, in the order shown, with a dash between them. For rectangular ranges, the default direction of concatenation is across the columns of a row before advancing to the next row. If you follow the rectangular range with a pipe-symbol (|) in quotes, then the direction of processing changes to down the rows of a column before advancing to the next column. Consider the following portion of a worksheet and note the differing order of the number words in each rectangular range.

	B	C	D	E	F
1	.
2		one	three	five
3		two	four	six
4
5		seven	eight	nine
6		ten	eleven	twelve
7					.

The formula...

=ConCatFormat(", ",C2:E3,"|",C5:E6)

will produce this output...

one, two, three, four, five, six, seven, ten, eight, eleven, nine, twelve

Then "|" argument is not concatenated into the outputted text because it only serves as a signal to the function to concatenate down the columns before advancing across the columns. If the preceding argument was not a range, then the pipe symbol would have been outputted normally. So, you might be wondering, how, in the unlikely event you needed to, would you output a pipe symbol that follows a range without having it be interpretted as a signal to change the direction of concatenation? Simply double it up like this...

=ConCatFormat(", ",C2:E3,"||",C5:E6)

If you needed to change the direction of concatenation and output a pipe symbol as well, just use two pipe symbols as the argument instead, like this...

=ConCatFormat(", ",C2:E3,"|","|",C5:E6)

The first pipe symbol will be used as the direction of concatenation indicator and the second one as a simple text character. Note, the pipe symbol immediately following any range (even a single column or single row one) will be interpretted as an indicator to change the direction of concatenation. For single row or column ranges, following them with a pipe symbol or not following them with one, the output will be the same.

The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...

=ConCatFormat("",A1:B3,C1,"HELLO",E1:E2)

or like this (note the leading comma)...

=ConCatFormat(,A1:B3,C1,"HELLO",E1:E2)

your choice.