Extract and Concatenate the Last Populated Values in Excel Rows
Question details
Find the second-to-last populated value in each row and concatenate the values through the third-to-last populated entry.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has values across each row, and the user needs formulas that work as the last populated column changes.
- Observed behavior
- The desired results are the second-to-last populated value and a space-separated string of earlier populated values.
Identify the columns containing the values to evaluate. The examples use B2:I2 as the source range and count nonempty cells as entries, including numbers and cells containing zero.
Use FILTER and INDEX to extract and join row values
Use these formulas in Excel versions that support dynamic arrays, LET, FILTER, and SEQUENCE.
Both formulas ignore empty cells and formulas that return an empty string. The join starts in column B and excludes the final two populated entries, even when there are gaps in the row.
In an empty cell outside B2:I2, enter =IFERROR(LET(v,FILTER(B2:I2,B2:I2<>""),n,COLUMNS(v),IF(n<2,"",INDEX(v,1,n-1))),"").
In another empty cell, enter =IFERROR(LET(v,FILTER(B2:I2,B2:I2<>""),n,COLUMNS(v),IF(n<3,"",TEXTJOIN(" ",TRUE,INDEX(v,1,SEQUENCE(1,n-2))))),"").
Select both formula cells and drag their fill handles down. If your data uses other columns, replace every B2:I2 reference with the appropriate row range.

Extract and Join Row Values in WPS Spreadsheets
Use helper columns in WPS Spreadsheets to locate the final two populated cells, return the second-to-last value, and join the preceding entries.
- 1. Open the workbook: Open the file in WPS Spreadsheets and check that B2:I2 contains the row data and J2:M2 are empty.
- 2. Locate the last entry: In J2, enter =IFERROR(LOOKUP(2,1/(B2:I2<>""),COLUMN(B2:I2)),0).
- 3. Locate the previous entry: In K2, enter =IFERROR(LOOKUP(2,1/((B2:I2<>"")*(COLUMN(B2:I2)<J2)),COLUMN(B2:I2)),0).
- 4. Return and join values: In L2, enter =IF(K2=0,"",INDEX(B2:I2,1,K2-COLUMN(B2)+1)). In M2, enter =IF(K2<=COLUMN(B2),"",TEXTJOIN(" ",TRUE,B2:INDEX(B2:I2,1,K2-COLUMN(B2)))).
- 5. Fill down: Select J2:M2 and drag the fill handle down for the remaining rows. Check that your WPS version supports TEXTJOIN and evaluates the LOOKUP array expressions.

Frequently Asked Questions
Do the formulas work when there are blank cells between values?
Yes. The Excel FILTER formulas count nonempty cells rather than assuming that populated columns are consecutive.
How do I change the separator in the joined result?
In the TEXTJOIN formula, replace the first argument, " ", with another separator, such as ", ", to use commas and spaces.
Why does the result show a blank?
The extraction formula needs at least two populated entries, and the join formula needs at least three. IFERROR also returns a blank if the formula encounters an error.
Can I include a different set of columns?
Yes. Replace every B2:I2 reference in the Excel formulas with the desired range, such as A2:H2, and fill the formulas down.




