Function Problems

How to Extract Text, Row Labels, and Week Numbers in Excel

John WilsonJohn Wilson Nov 10, 2026 869 views

Question details

The user wants to generate a combined flat list from a two-dimensional data range, appending specific row labels and column headers to each nonblank cell value.

How to Extract Text, Row Labels, and Week Numbers in Excel
Product
Excel
Device & OS
not provided
Scenario
Transforming a 2D data matrix into a 1D column list for better data analysis, specifically combining row metadata, column metadata, and cell values.
Observed behavior
The goal state is a cleanly formatted single column containing concatenated text, row labels, and week numbers for all nonblank cells in the target range.
Before you start

Ensure your spreadsheet software supports modern dynamic array functions such as LET, TOCOL, and FILTER. Identify the exact ranges for your data, row labels, and column headers before writing the formula.

Solution 1Recommended

Use LET, TOCOL, and FILTER Dynamic Arrays

This modern method cleanly processes the 2D range, removes blank cells, and concatenates the row and column headers into a unified list.

The LET function allows you to define variables within your formula, making complex dynamic array operations much easier to read and manage. By combining it with TOCOL and FILTER, you can flatten a 2D grid into a 1D list while ignoring empty cells.

1
Select the destination cell

Click on the cell where you want the new extracted list to begin. Ensure there is enough empty space below it to accommodate the spilled array results.

2
Input the LET formula

Type the following formula: =LET(data,C6:AB14,labels,B6:B14,weeks,C3:AB3,values,TOCOL(data),rowLabels,TOCOL(IF(data<>"",labels,"")),weekLabels,TOCOL(IF(data<>"",weeks,"")),FILTER(rowLabels&" - "&weekLabels&" - "&values,values<>""))

3
Adjust ranges and execute

Modify C6:AB14 (data), B6:B14 (row labels), and C3:AB3 (week numbers) to match your actual worksheet ranges, then press Enter to generate the dynamic array.

Use LET, TOCOL, and FILTER Dynamic Arrays
Dynamic Updates: This formula automatically updates the extracted list if you add, remove, or modify data within the specified ranges.

Extract and Manage Complex Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like TOCOL, FILTER, and LET, allowing you to effortlessly transform 2D data ranges into flat lists.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your 2D data range.
  2. 2. Select the target cell: Click on the cell where you want the flattened list to appear.
  3. 3. Enter the formula: Type in your dynamic array formula using TOCOL and FILTER.
  4. 4. Generate the list: Press Enter to instantly flatten and concatenate your 2D data into a single column.
Fully compatible with Microsoft Excel formulas and dynamic arrays.Lightweight and fast, even when processing large datasets.Built-in data analysis tools to help you manage extracted lists.Free to use with a familiar, user-friendly interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my TOCOL formula return a #NAME? error?

The #NAME? error usually occurs if your spreadsheet software version does not support modern dynamic array functions like TOCOL. Ensure you are using an updated version of WPS Office or Microsoft 365.

Can I change the separator between the row label and week number?

Yes, you can modify the text string in the formula. For example, replace " - " with ", " or " | " in the concatenation part of the formula to change the separator to a comma or a pipe.

How do I ignore errors as well as blanks in the data range?

You can use the second argument of the TOCOL function. Setting it to 3 (e.g., =TOCOL(array, 3)) will ignore both blanks and errors when flattening the array.

Does this formula work if my data range has merged cells?

Dynamic array formulas often struggle with merged cells, which can cause #SPILL! errors or incorrect referencing. It is highly recommended to unmerge cells in your data range before applying the formula.