Concatenate more than 10 words

[saru5133]

Hi,

I am trying to concatenate a row which has some 150 entries

=A1&","&A2&","&A3","&A4...... and on like this.
But i find it teadous to do each and every cell.

Any solution to simplify?

[HansV]

You could create the following custom function in a module in the Visual Basic Editor:

Code: Select all

Function Concat(rng As Range, Optional sep As String = ",") As String
  Dim rngCell As Range
  Dim strResult As String
  For Each rngCell In rng
    If rngCell.Value <> "" Then
      strResult = strResult & sep & rngCell.Value
    End If
  Next rngCell
  If strResult <> "" Then
    strResult = Mid(strResult, Len(sep) + 1)
  End If
  Concat = strResult
End Function

Use like this in a cell formula:

=CONCAT(A1:A12)

to concatenate the values separated by commas (the default), or

=CONCAT(A1:A12," - ")

to concatenate the values separated by spaces and dashes (hyphens).

[Rudi]

Very nice function Hans.

[Rick Rothstein]

Here is an extended concatenation UDF that I have posted in the past which works differently than the one Hans posted (in case you ever need the added functionalitly)...

Code: Select all

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String

    Dim Cell As Range, Area As Variant

    If IsMissing(Delimiter) Then Delimiter = ""

    For Each Area In CellRanges
        If TypeName(Area) = "Range" Then
            For Each Cell In Area
                If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
            Next
        Else
            ConCat = ConCat & Delimiter & Area
        End If
    Next

    ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

This function allows you to create formulas like this...

=ConCat("-",A1:A3,C1,"HELLO",D1:D2)

and the contents of the indicated cells, and the word "HELLO", will be concantenated together, in the order shown, with a dash between them. 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:A3,C1,"HELLO",D1:D2)

or like this (note the leading comma)...

=ConCat(,A1:A3,C1,"HELLO",D1:D2)

your choice.
 

[saru5133]

fabulous....you guys a playing with macros on tips.

Thanks once again...

[Rudi]

Thanks Rick. This is a very nifty UDF. It really amazes me what Excel (and a bit of code in Excel) can do!!! It is also surprising that Microsoft does not enhance their built in functions to operate like these. It would make it even more powerful as a business tool.

TX again! Awesome function!