How to return text from visible cells?

I want to create a cell that returns the text from visible cells, to link this cell to a chart title. Functions lijke AGGREGATE and SUBTOTAL returns only numbers from visible cells. Is there a way to return text?

2 answers

  • answered 2018-07-15 14:50 Solar Mike

    Without any type of example, I can only offer the use of the & ...


    ="This is "&C15

    where cell C15 contains the text "a test"

    Or if B15 contains "This is " then:


  • answered 2018-07-15 15:32 FocusWiz

    I am likewise unclear what is needed. However, I created this public function to do something like what you requested:

    Option Explicit
    Public Function ConcatVisibleWithSeparator(rngRange As Range, strSeparator As String) As String
        Dim rngCell As Range
        Dim strReturn As String
        For Each rngCell In rngRange
            If rngCell.EntireRow.Hidden = False Then
                strReturn = strReturn & rngCell.Value & strSeparator
            End If
        Next rngCell
        ConcatVisibleWithSeparator = Left(strReturn, Len(strReturn) - Len(strSeparator))
    End Function

    It is called with an entry in a cell like this:

    =ConcatVisibleWithSeparator(B2:B7," ")

    What it does is concatenate everything in the specified range with the specified separator in between values.

    Two examples are attached as visuals.

    Here:Before Rows are Hidden

    and here:After Rows are Hidden