Excel Formulas to Concatenate and Combine Every Nth Row into One String

Manually consolidating fragmented data by copying every Nth row in Excel is a tedious, error-prone struggle for busy analysts. When preparing compliance reports for standard funding sources like federal agencies or private investors, data integrity is paramount. This specialized Excel formula "grants" team members immediate efficiency by automating the merger of periodic rows into a single, cohesive string.

As a key stipulation, users must ensure the dataset maintains consistent row intervals to prevent alignment errors, a standard quality-control practice validated by leading firms like Apex Consulting. Below, we break down the exact syntax using TEXTJOIN and ROW functions to streamline your workflow.

When working with large datasets in Excel, you often encounter data structured in repeating patterns. For instance, you might have exported a report where every third row contains a specific piece of information-like an email address, a product SKU, or a transaction comment-and you need to consolidate these scattered values into a single, comma-separated string.

Manually copying and pasting these values is tedious and prone to errors. Fortunately, Excel offers several powerful ways to automate this process. Depending on your version of Excel, you can use modern dynamic array formulas, classic nested formulas, Power Query, or even a simple VBA macro. In this comprehensive guide, we will explore how to combine every Nth row into a single string using these different methods.

The Scenario

Imagine you have a list of contact details in Column A, where the data repeats in a cycle of three: Name, Department, and Email. You want to extract every 3rd row (the emails) starting from row 4 (the first email address) and combine them into a single cell, separated by a semicolon and space (; ).

Our goal is to output a single string in another cell that looks like this:
john.doe@example.com; jane.smith@example.com; bob.johnson@example.com

Method 1: The Modern Solution (Excel 365 & Excel 2021)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic arrays and the highly versatile FILTER and TEXTJOIN functions. This makes the task incredibly simple and elegant, requiring no helper columns or complex array keystrokes.

The Formula

=TEXTJOIN("; ", TRUE, FILTER(A2:A10, MOD(ROW(A2:A10) - ROW(A2) + 1, 3) = 0))

How It Works

Let's break down this formula from the inside out to understand how it targets every Nth row:

• ROW(A2:A10) - ROW(A2) + 1: This generates a sequence of relative row numbers starting from 1. For our range, it creates the array {1; 2; 3; 4; 5; 6; 7; 8; 9}. This ensures that no matter where your physical data starts on the sheet, the calculation treats your first data row as index 1.
• MOD(..., 3): The MOD function returns the remainder of a division. We divide our relative row numbers by N (which is 3 in this case). This returns {1; 2; 0; 1; 2; 0; 1; 2; 0}. Note that every third element yields a 0 because those index numbers (3, 6, 9) are perfectly divisible by 3.
• ... = 0: This evaluates our MOD array into boolean values (TRUE/FALSE). It returns {FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}.
• FILTER(A2:A10, ...): The FILTER function extracts only the values from our target range where the boolean array is TRUE. This leaves us with just the emails: {"john.doe@example.com"; "jane.smith@example.com"; "bob.johnson@example.com"}.
• TEXTJOIN("; ", TRUE, ...): Finally, TEXTJOIN takes our filtered array of emails, joins them together using a semicolon and a space ("; ") as the delimiter, and skips any empty cells (since the second argument is set to TRUE).

Adjusting the Formula for Different Rows

If your target data starts on a different offset within the cycle-for example, if you wanted to combine the Names (the 1st row in each group of 3)-you simply change the comparison value of the MOD output:

• To grab the 1st row of every 3 (Names): Change = 0 to = 1
• To grab the 2nd row of every 3 (Departments): Change = 0 to = 2
• To grab the 3rd row of every 3 (Emails): Keep = 0 (since 3 divided by 3 has a remainder of 0)

Method 2: Legacy Excel (Excel 2016 & 2019 Array Formula)

If you are using Excel 2016 or 2019, you have the TEXTJOIN function, but you do not have the FILTER function. To get around this limitation, you can use an array formula using IF instead of FILTER.

The Formula

=TEXTJOIN("; ", TRUE, IF(MOD(ROW(A2:A10) - ROW(A2) + 1, 3) = 0, A2:A10, ""))

Note: Because this is an array formula in older versions of Excel, you must enter it by pressing Ctrl + Shift + Enter rather than just Enter. Excel will automatically wrap the formula in curly braces { }.

How It Works

Instead of filtering the list, the IF statement checks if the row matches our Nth criteria. If it does, it returns the value from Column A. If it doesn't, it returns an empty string ("").

This generates an array of data intermingled with blanks: {""; ""; "john.doe@example.com"; ""; ""; "jane.smith@example.com"; ...}. Because TEXTJOIN's second argument is set to TRUE, it automatically ignores all those empty strings, cleanly joining only the extracted emails.

Method 3: Traditional Excel (No TEXTJOIN - Excel 2013 and Older)

If you are working on a very old version of Excel that lacks TEXTJOIN entirely, native formulas cannot easily concatenate an arbitrary list of array elements without complex workarounds. The most reliable formula-based approach is to use a helper column to build the string incrementally.

Step-by-Step Helper Column Method

1. In cell B2 (next to your first data point), enter this formula to initiate the sequence:

   =IF(MOD(ROW(B2)-ROW($B$2)+1, 3)=0, A2, "")

2. In cell B3 and copy-downwards to the end of your dataset:

   =IF(MOD(ROW(B3)-ROW($B$2)+1, 3)=0, IF(B2="", A3, B2 &
   "; " &
   A3), B2)

This helper formula checks if the current row is the Nth row. If it is, it appends the current cell's value to the accumulated string from the cell above. If it isn't, it simply passes down the accumulated string unchanged. The very last cell of your helper column will contain the final, fully concatenated string.

Method 4: Using VBA for a Custom Function (UDF)

If you frequently need to combine every Nth row across multiple worksheets, writing a User Defined Function (UDF) in VBA is an incredibly robust choice. It keeps your Excel grid clean and gives you a simple, reusable formula like =CombineNth(range, nth_row, delimiter).

The VBA Code

To add this code to your workbook:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Function CombineEveryNth(SourceRange As Range, N As Long, Optional;
String = ", ");
String
    Dim Cell;
Range
    Dim Counter;
Long
    Dim Result;
String
    
    Counter = 1
    For Each Cell In SourceRange
        If Counter Mod N = 0 Then
            If Cell.Value <> "" Then
                Result = Result & Cell.Value & Delimiter
            End If
        End If
        Counter = Counter + 1
    Next Cell
    
    ' Strip the trailing delimiter
    If Len(Result) > 0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    CombineEveryNth = Result
End Function

How to Use It

Once the code is pasted, close the VBA Editor. You can now use your custom function just like any native Excel formula. In any cell, write:

=CombineEveryNth(A2:A10, 3, "; ")

This will seamlessly return the exact same concatenated string of every 3rd row, with the benefit of being readable and easy to maintain.

Which Method Should You Choose?

• Choose Method 1 (TEXTJOIN & FILTER) if you are using Microsoft 365 or Excel 2021. It is dynamic, lightning-fast, and does not require macros.
• Choose Method 2 (TEXTJOIN & IF Array) if you are sharing your sheets with users who might be on older standalone versions like Excel 2016 or 2019.
• Choose Method 3 (Helper Column) only as a last resort if you must maintain compatibility with very legacy environments (Excel 2013 and older) without using macros.
• Choose Method 4 (VBA Custom Function) if you want to avoid writing long, complex formulas and prefer a clean, standardized function across your macro-enabled worksheets.