How to append a series of cells in a fast manner for an Excel worksheet

I need to append or concatenate cell J3 thru LN3.

I can do it mannual like so:

             =J3&K3&L3&M3&N3&O3&P3&Q3&.... and so on

Is there a function or a way to do it faster?
rayluvsAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

the concatenate function is seriously lacking. Many people have written their own function with VBA to overcome this. One example is at Chandoo's site:

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

cheers, teylyn
0
 
wuyinzhiCommented:
maybe:

=CONCATENATE(J3:Q3)
0
 
Patrick MatthewsConnect With a Mentor Commented:
To do that you need a UDF, such as this:

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
    ' value

    ' 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
    ' spaces)
    
    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))
        End If
    Next CLL

    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window




Syntax:


=ConcRange(J3:LN3)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
See attached for a sample file with the User Defined Function in a standard VBA module.

cheers, teylyn
Book3.xlsm
0
 
krishnakrkcConnect With a Mentor Commented:
Hi,

Another UDF.

Use like

=kCONCAT(J3:N3)

If blanks to be skipped, then

=kCONCAT(IF(J3:N3<>"",J3:N3))

Array entered.

Kris
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)
        End If
    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)
        Else
            kCONCAT = Join$(Application.Transpose(ConcatRange), Delim)
        End If
        kCONCAT = Replace(Replace(kCONCAT, ",False", ""), "False,", "")
    End If
End Function

Open in new window

0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.