• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate a variable range

Asheesh

Asheesh

Excel Ninja
hi Experts, this one is giving me a real hard time...I have a variable range say Column A..eg A1:A5...A1 contains text1, A2 has text2, A3 has text3..so on and so forth..I know I can concatenate them with helper columns and get all the values in one cell...however, I am looking for a single cell formula without helper columns to concatenate these values..
Result should be B1 something like this- text1,text2,text3,.....

Appreciate your help on this...
 
Asheesh said:
hi Experts, this one is giving me a real hard time...I have a variable range say Column A..eg A1:A5...A1 contains text1, A2 has text2, A3 has text3..so on and so forth..I know I can concatenate them with helper columns and get all the values in one cell...however, I am looking for a single cell formula without helper columns to concatenate these values..
Result should be B1 something like this- text1,text2,text3,.....

Appreciate your help on this...
Click to expand...
Hi Asheesh,
You want comma between the text1 & text2.

REgards,
Lakshmi Narain
 
Hi,

May be an UDF like this

Code: 
Function MYCONCATENATE(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
 
'Formula Example =MYCONCATENATE("_",A1:E1)
 
 
  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
              MYCONCATENATE = MYCONCATENATE & 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 MYCONCATENATE = MYCONCATENATE & Delimiter & Cell.Value
        Next
      End If
    Else
      If CellRanges(Index) = "||" Then
        MYCONCATENATE = MYCONCATENATE & Delimiter & "|"
      Else
        MYCONCATENATE = MYCONCATENATE & Delimiter & CellRanges(Index)
      End If
    End If
    Index = Index + 1
  Loop
  MYCONCATENATE = Mid(MYCONCATENATE, Len(Delimiter) + 1)
End Function
 
Hi,

As far as my knowledge I don't think Concatenate without selecting each cell will not be possible other than through VBA
 
You must log in or register to reply here.
Back
Top