Results 1 to 2 of 2

Thread: FORMATTED Flexible Concatenation Function

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    FORMATTED Flexible Concatenation Function

    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.
    Last edited by Rick Rothstein; 09-25-2012 at 10:57 PM.

  2. #2
    Junior Member
    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0
    Can you please let me know how to extend this function so it Concatenate with a criteria. something like some SumIf equivelent.

    Thanks.

Similar Threads

  1. Flexible Concatenation Function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 23
    Last Post: 05-11-2019, 08:22 PM
  2. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •