none
how to aggregate a row range RRS feed

  • Question

  • Hi all,

    I often have to aggregate a row range , e.g. A1: F1 whose data are

    1  2  3  4  5  6,

    into a comma separated string in this format "1,2,3, 4,5,6". It can be hard-coded in a formula , A1&","B1&","....

    Just wondering if there is a more concise way rather than to enumerate all cells in case dozens of them?

    thanks a lot,

    Sheldon

    Tuesday, May 5, 2020 3:34 PM

Answers

  • Here is a custom VBA function:

    Function ConcatRange(ConcatenateRange As Range, Optional Separator As String = ",", _
            Optional AsDisplayed As Boolean) As Variant
        Dim cel As Range
        Dim strResult As String
        On Error GoTo ErrHandler
        For Each cel In ConcatenateRange
            If AsDisplayed Then
                strResult = strResult & Separator & cel.Text
            Else
                strResult = strResult & Separator & cel.Value
            End If
        Next cel
        If strResult <> "" Then
            strResult = Mid(strResult, Len(Separator) + 1)
        End If
        ConcatRange = strResult
        Exit Function
    ErrHandler:
        ConcatRange = CVErr(xlErrValue)
    End Function

    Use like this:

    =ConcatRange(A1:F1)


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, May 7, 2020 9:20 PM

All replies

  • If you have Office 365 or Office 2019, you can use

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


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Tuesday, May 5, 2020 6:40 PM
  • Question
    You cannot vote on your own post
    0

    thanks Hans,

    I only have Excel 2013. Anything can we do with it? To me, the concatenate() just needs a little extension.

    thanks,

    Sheldon

    Thursday, May 7, 2020 6:07 PM
  • Here is a custom VBA function:

    Function ConcatRange(ConcatenateRange As Range, Optional Separator As String = ",", _
            Optional AsDisplayed As Boolean) As Variant
        Dim cel As Range
        Dim strResult As String
        On Error GoTo ErrHandler
        For Each cel In ConcatenateRange
            If AsDisplayed Then
                strResult = strResult & Separator & cel.Text
            Else
                strResult = strResult & Separator & cel.Value
            End If
        Next cel
        If strResult <> "" Then
            strResult = Mid(strResult, Len(Separator) + 1)
        End If
        ConcatRange = strResult
        Exit Function
    ErrHandler:
        ConcatRange = CVErr(xlErrValue)
    End Function

    Use like this:

    =ConcatRange(A1:F1)


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, May 7, 2020 9:20 PM
  • sorry for the delayed reply and thank you very much.

    thanks,

    Saturday, May 30, 2020 11:08 AM