Excel Formula to Combine and Concatenate Filtered Rows with Semicolons

Manually concatenating filtered spreadsheet data remains a tedious bottleneck for financial analysts. When tracking standard capital allocations or federal funding sources, bridging disparate rows into a single, cohesive report is historically cumbersome. Fortunately, leveraging dynamic formulas grants immediate analytical clarity by automating this synthesis.

As a brief stipulation, this advanced method requires modern Excel engines (Office 365 or 2021) to support dynamic arrays. Organizations successfully utilize this to aggregate compliance data, such as merging multiple EPA grant codes into a single, semicolon-delimited cell.

Below, we will examine the precise step-by-step formula combining TEXTJOIN and FILTER to streamline your reporting workflow.

Excel is an incredibly powerful tool for data analysis, but one of its most common challenges is working with data that has been filtered. When you apply a filter to a dataset, the hidden rows are still technically there-they are just tucked out of sight. This becomes a significant roadblock when you try to concatenate or combine those visible rows into a single cell separated by a semicolon.

If you use the standard CONCAT or TEXTJOIN functions on a filtered range, Excel will frustratingly include all the hidden rows in your final output. In this comprehensive guide, we will explore several robust formulas and techniques to combine only the filtered, visible rows in Excel with a semicolon, ranging from modern dynamic array formulas to backward-compatible helper columns and VBA solutions.

The Goal

Imagine you have a list of email addresses, project IDs, or task names in column A. You filter the table to show only "High Priority" items. You now want to copy all the visible emails or IDs into a single cell, separated by a semicolon (;), so you can easily copy-paste them into an email client or another system. Here is how to achieve exactly that.

Method 1: The Modern Excel 365 Formula (No Helper Columns)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic arrays and Lambda helper functions. We can combine TEXTJOIN, FILTER, BYROW, and SUBTOTAL to create a single-cell formula that updates dynamically as you change your manual filters.

The Formula:

=TEXTJOIN("; ", TRUE, FILTER(A2:A20, BYROW(A2:A20, LAMBDA(row, SUBTOTAL(103, row)))))

How It Works:

• SUBTOTAL(103, row): The function number 103 corresponds to COUNTA while ignoring hidden rows. If a row is visible, SUBTOTAL returns 1. If it is filtered out (hidden), it returns 0.
• BYROW(A2:A20, LAMBDA(...)): Normally, SUBTOTAL cannot evaluate an array row-by-row on its own. The BYROW function forces Excel to look at each individual row in our range and apply the SUBTOTAL check, generating an array of 1s (visible) and 0s (hidden).
• FILTER(A2:A20, ...): This filters our original range, keeping only the cells where the BYROW array returned 1 (visible).
• TEXTJOIN("; ", TRUE, ...): Finally, this joins the filtered array of text strings, using a semicolon and a space ("; ") as the delimiter, and ignores any empty cells (TRUE).

Method 2: The Helper Column Approach (Works in Excel 2019 & 2021)

If you do not have Microsoft 365 (meaning you lack the BYROW and LAMBDA functions) but still have Excel 2019, you can easily solve this problem using a single helper column. This is often the most stable and easiest-to-understand method for legacy versions.

Step 1: Create the Helper Column

In an empty column next to your data (let's say Column B, starting at B2), enter the following formula and drag it down to match your data rows:

=SUBTOTAL(103, A2)

This formula will output a 1 if the row is visible and a 0 if the row is hidden by a filter.

Step 2: Use the TEXTJOIN Formula

Now, in the cell where you want your concatenated, semicolon-separated list, write the following formula:

=TEXTJOIN("; ", TRUE, IF(B2:B20=1, A2:A20, ""))

Note: If you are using Excel 2019, you may need to press Ctrl + Shift + Enter to enter this as an array formula.

Alternative using FILTER: If you have Excel 2021, you can write a cleaner version without the array entry:

=TEXTJOIN("; ", TRUE, FILTER(A2:A20, B2:B20=1))

Method 3: Combining Rows Based on Criteria (Avoiding Manual Filters)

Sometimes, we filter a list manually just to extract specific data. If you are doing this, you can bypass the manual filter UI altogether. You can write a formula that dynamically filters the data based on your criteria and joins them instantly. This prevents you from needing to manually click filter buttons.

Suppose Column A contains the values you want to combine (Emails), and Column B contains the status ("Active", "Inactive"). You only want to combine emails where the status is "Active".

The Formula:

=TEXTJOIN("; ", TRUE, FILTER(A2:A20, B2:B20="Active"))

This is highly recommended because it is completely automated. If you change a status in Column B, your combined semicolon list updates instantly without you needing to re-apply manual filters.

Method 4: The VBA User-Defined Function (UDF) for Ultimate Compatibility

If you are working with older Excel versions (like Excel 2016 or 2013) that do not have the TEXTJOIN or FILTER functions, or if you want an incredibly simple formula syntax, a quick VBA macro is the best approach.

The VBA Code:

Press ALT + F11 to open the VBA editor, click Insert > Module, and paste the following code:

Function JoinVisible(WorkRange As Range,;
String);
String
    Dim Cell;
Range
    Dim Result;
String
    
    For Each Cell In WorkRange
        ' Check if the row and column of the cell are not hidden
        If Cell.EntireRow.Hidden = False And Cell.EntireColumn.Hidden = False Then
            If Cell.Value <> "" Then
                Result = Result & Cell.Value & Delimiter
            End If
        End If
    Next Cell
    
    ' Remove the trailing delimiter
    If Len(Result) > 0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    JoinVisible = Result
End Function

How to Use It:

Close the VBA window and return to your Excel sheet. Now, you can use this brand-new custom function just like any native Excel formula:

=JoinVisible(A2:A20, "; ")

This custom function automatically loops through your range, completely ignores any cells hidden by filters or manual row hiding, skips blanks, and concatenates them with your specified delimiter.

Method 5: Power Query (The Corporate Solution)

If you are dealing with very large datasets (thousands of rows) and need to output combined, filtered strings regularly, Power Query is the cleanest, non-formula way to handle this.

1. Select your data range and go to the Data tab > From Table/Range to load it into Power Query.
2. Use the column filters in Power Query to filter out the rows you don't want (just like you would in Excel).
3. Select the column you want to combine. Go to the Transform tab and click Group By.
4. In the Group By settings:
   • Set operation to Sum (we will change this in the formula bar because text cannot be summed natively).
5. Once grouped, look at the Power Query formula bar. It will say something like List.Sum([ColumnName]). Change List.Sum to:

   Text.Combine([ColumnName], "; ")

6. Click Close & Load to return the clean, combined list back to Excel.

Summary of Solutions: Which One Should You Choose?

By using these tailored strategies, you can easily control how Excel processes your hidden data, allowing you to quickly format and export clean, semicolon-separated lists from any filtered view.