Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

concatenate 6k cells - automate creating the formula

how can i create a concatenate formula to combine the contents of 6k cells

i would rather not type each cell number separated by a comma!
but a range does not work either

how can i create a list of all those cells?
0
finnstone
Asked:
finnstone
  • 2
2 Solutions
 
Patrick MatthewsCommented:
I'd use a UDF for this.  Add the following code to a regular VBA module:

Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False, _
    Optional TrimSpaces As Boolean = False)
    
    ' Function by Patrick Matthews, Matt Vidas, and rberke
    ' Revised based on suggestions from Dave Brett and Mark Hutchinson based on comments
    ' at http://www.experts-exchange.com/A_7811.html
    
    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If SkipBlanks=True, blank 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).  To use
    ' multiple areas, enclose the various ranges in parentheses, e.g.:
    ' =ConcRange((A1:A10,Q14:Q17,Z200:Z300),";")
    
    ' 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 in the Substrings
    ' range when it performs the concatenation.  If SkipBlanks=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
    
    ' TrimSpaces: Indicates whether leading or trailing spaces are removed from substrings
    ' prior to concatenation.  Multiple internal spaces are ignored, as the VBA Trim() and
    ' not Excel's TRIM() is used.  Space trimming is performed against actual or displayed
    ' value, as determined by the AsDisplayed argument.  If SkipBlanks and TrimSpaces are
    ' both True, "blank" is determined based on trimmed value or displayed text
    
    Dim CLL As Range
    
    If AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Text) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Text))
            End If
        Next
    ElseIf AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Text <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Text)
            End If
        Next
    ElseIf AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Text))
        Next
    ElseIf AsDisplayed And Not SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Text)
        Next
    ElseIf Not AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Value) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Value))
            End If
        Next
    ElseIf Not AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Value <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Value)
            End If
        Next
    ElseIf Not AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Value))
        Next
    Else
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Value)
        Next
    End If
        
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window


The use a formula like this:

=ConcRange(A1:A6000,",")
0
 
Patrick MatthewsCommented:
BTW, if you are using Excel 2016, you can also use TEXTJOIN:

=TEXTJOIN(",",TRUE,A1:A6000)

That second argument indicates whether or not to ignore blank entries.
1
 
Rob HensonIT & Database AssistantCommented:
With Excel 2016 you also get the CONCAT function. Syntax and use from office Support shown here:

https://support.office.com/en-gb/article/CONCAT-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

Both CONCAT and TEXTJOIN have a limitation of 32,767 characters for the resulting string; so that gives you an average of just over 5 characters per cell if looking at 6000 cells. Will that be enough?
0
 
finnstoneAuthor Commented:
badass answers thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now