How to Extract Text, Row Labels, and Week Numbers in Excel
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.

- 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.
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.
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.
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.
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<>""))
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 TOCOL with IF and Concatenation
A slightly shorter alternative that concatenates the ranges directly and then flattens them into a single column.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing your 2D data range.
- 2. Select the target cell: Click on the cell where you want the flattened list to appear.
- 3. Enter the formula: Type in your dynamic array formula using TOCOL and FILTER.
- 4. Generate the list: Press Enter to instantly flatten and concatenate your 2D data into a single column.

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.




