See this link FORMATTED Flexible Concatenation Function for this article's companion function.
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...
This function allows you to create formulas like this...Code:Function ConCat(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 ConCat = ConCat & Delimiter & Rng(1).Offset(Rw, Col) End If Next Next Index = Index + 1 Else For Each Cell In Intersect(Rng, Rng.Parent.UsedRange) If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value Next End If Else If CellRanges(Index) = "||" Then ConCat = ConCat & Delimiter & "|" Else ConCat = ConCat & Delimiter & CellRanges(Index) End If End If Index = Index + 1 Loop ConCat = Mid(ConCat, Len(Delimiter) + 1) End Function
=ConCat("-",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...
=ConCat(", ",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...
=ConCat(", ",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...
=ConCat(", ",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...
=ConCat("",A1:B3,C1,"HELLO",E1:E2)
or like this (note the leading comma)...
=ConCat(,A1:B3,C1,"HELLO",E1:E2)
your choice.
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function (as shown in the examples above).
Reply With Quote
Originally Posted by snb
Bookmarks