Learn when you want, where you want with convenient online training courses. Sign up now!
[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Function ConcRange(Substrings As Range, Optional Delim As String = "", _
Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)
' Function by Patrick Matthews, Matt Vidas, and rberke
' Concatenates a range of cells, using an optional delimiter. The concatenated
' strings may be either actual values (AsDisplayed=False) or displayed values.
' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
' are skipped in the concatenation
' Substrings: the range of cells whose values/text you want to concatenate. May be
' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)
' Delimiter: the optional separator you want inserted between each item to be
' concatenated. By default, the function will use a zero-length string as the
' delimiter (which is what Excel's CONCATENATE function does), but you can specify
' your own character(s). (The Delimiter can be more than one character)
' AsDisplayed: for numeric values (includes currency but not dates), this controls
' whether the real value of the cell is used for concatenation, or the formatted
' displayed value. Note for how dates are handled: if AsDisplayed is FALSE or omitted,
' dates will show up using whatever format you have selected in your regional settings
' for displaying dates. If AsDisplayed=TRUE, dates will use the formatted displayed
' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
' nothing but spaces) in the Substrings range when it performs the concatenation.
' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
' concatenation. In the examples above, where NoBlanks=False, you will see "extra"
' delimiters in cases where the Substrings range has blank cells (or cells with only
Dim CLL As Range
For Each CLL In Substrings.Cells
If Not (SkipBlanks And Trim(CLL) = "") Then
ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
ConcRange = Mid$(ConcRange, Len(Delim) + 1)
Open in new window
Function kCONCAT(ByRef ConcatRange, Optional ByVal Delim As String = ",") As String
Dim UpperDimension As Long
If TypeOf ConcatRange Is Range Then
If (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count = 1) Then
kCONCAT = Join$(Application.Transpose(ConcatRange.Value), Delim)
ElseIf (ConcatRange.Rows.Count = 1) * (ConcatRange.Columns.Count > 1) Then
kCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange.Value)), Delim)
ElseIf (ConcatRange.Rows.Count > 1) * (ConcatRange.Columns.Count > 1) Then
kCONCAT = CVErr(xlErrNA)
ElseIf IsArray(ConcatRange) Then
On Error Resume Next
UpperDimension = UBound(ConcatRange, 2)
On Error GoTo 0
If UpperDimension = 0 Then
kCONCAT = Join$(Application.Transpose(Application.Transpose(ConcatRange)), Delim)
kCONCAT = Join$(Application.Transpose(ConcatRange), Delim)
kCONCAT = Replace(Replace(kCONCAT, ",False", ""), "False,", "")
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.