When working in Google Sheets, you may not always want to keep your data in its original table format — especially when creating summaries, compact reports, or exporting data for other tools. In such cases, you might need to concatenate header names (field labels) with the values of the cells below.

This approach makes your dataset more readable, saves space on the sheet, and creates export-friendly strings that combine the category and its value in one cell.

For example, instead of keeping quantities in separate columns for Mango, Orange, Banana, and Apple, you can merge each quantity with its corresponding header to get compact text like:

Mango 50, Orange 75, Apple 10
Banana 200, Apple 500

In this tutorial, you’ll learn step-by-step how to concatenate header names with the values of the cells below in Google Sheets using both regular and array formulas.

Sample Data

Let’s say we have a data table in the range A1:D where:

• Row 1 (A1:D1) contains header names: Mango, Orange, Banana, Apple.
• Rows below contain numeric values.
• Some cells are blank.

Example dataset:

Our goal is to combine each non-blank cell value with its corresponding header and return them as a comma-separated list in a single cell for each row.

For example:

Row 2 → "Mango 100, Banana 25"
Row 3 → "Mango 50, Orange 75, Apple 10"
Row 4 → "Apple 500"
Row 7 → "Mango 50, Apple 100"

Concatenate Headers with the Values of the Cells Below – Non-Array Formula

If you want a regular formula that you can drag down, use the following:

=IFNA(
   REGEXREPLACE(
      REGEXREPLACE(
         TEXTJOIN(" ", TRUE, 
            TRANSPOSE(
               VSTACK(
                  FILTER($A$1:$D$1, A2:D2<>""), 
                  TOROW(A2:D2, 3)
               )
            )
         ), 
         "(\s.*?){2}","$0,"
      ), 
      "\s\,",", "
   )
)

Steps:

1. Enter this formula in F2.
2. Drag the fill handle down or copy-paste into other rows.

Note: This method preserves formatting of original values (currency, percentage, date, etc.) in the combined result.

Formula Explanation

Let’s break it down:

• FILTER($A$1:$D$1, A2:D2<>"") → Returns only the header names where the corresponding row values are not blank.
• TOROW(A2:D2, 3) → Converts the row values into a vertical list (matching the header order).
• VSTACK(...) → Stacks the filtered headers above the corresponding values.
• TRANSPOSE(...) → Arranges them side by side for easier joining.
  
• TEXTJOIN(" ", TRUE, ...) → Joins the headers and values with spaces.
  Mango 100 Banana 25
• REGEXREPLACE(..., "(\s.*?){2}","$0,") → Inserts a comma after each value-header pair.
• REGEXREPLACE(..., "\s\,",", ") → Fixes any unwanted space-comma combinations.
• IFNA(...) → Handles empty rows without showing an error.

👉 Related: How to Replace Every Nth Delimiter in Google Sheets

Concatenate Headers with the Values of the Cells Below – Array Formula

If your dataset may grow, use an array formula so the results auto-expand without dragging down.

=BYROW(
   A2:D, 
   LAMBDA(r, 
      IFNA(
         REGEXREPLACE(
            REGEXREPLACE(
               TEXTJOIN(" ", TRUE, 
                  TRANSPOSE(
                     VSTACK(
                        FILTER(A1:D1, r<>""), 
                        TOROW(r, 3)
                     )
                  )
               ), 
               "(\s.*?){2}","$0,"
            ), 
            "\s\,",", "
         )
      )
   )
)

How it works:

• BYROW(A2:D, LAMBDA(...)) → Processes each row individually.
• Inside the LAMBDA, the same logic as the non-array formula is applied to each row.
• This eliminates the need to drag down formulas manually.

👉 Related: How to Use the BYROW Function in Google Sheets

Choosing the Right Formula for Your Dataset

• Non-array formula → If your dataset is static and unlikely to grow.
• Array formula → If your dataset changes often or you expect to add/remove rows.

Copy Sample Sheet

Related Tutorials

• Lookup and Retrieve Column Headers in Google Sheets
• Lookup Header and Filter Non-Blanks in Google Sheets
• Search Across Columns and Return the Header in Google Sheets
• HLOOKUP to Search Entire Table and Find the Header in Google Sheets
• Lookup First and Last Values in a Row and Return Headers in Google Sheets
• Find the Column Header of the Max Value in Google Sheets
• Find the Column Header of the Min Value in Google Sheets