Function Problems

Extract and Concatenate the Last Populated Values in Excel Rows

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Find the second-to-last populated value in each row and concatenate the values through the third-to-last populated entry.

Extract and Concatenate the Last Populated Values in Excel Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Return the second-to-last value

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))),"").

2
Join the earlier values

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))))),"").

3
Apply formulas to other rows

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.

Use FILTER and INDEX to extract and join row values
Rows with too few entries: The extraction formula returns a blank for fewer than two populated cells. The join formula returns a blank for fewer than three.
Work with row data in WPS Spreadsheets

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. 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. 2. Locate the last entry: In J2, enter =IFERROR(LOOKUP(2,1/(B2:I2<>""),COLUMN(B2:I2)),0).
  3. 3. Locate the previous entry: In K2, enter =IFERROR(LOOKUP(2,1/((B2:I2<>"")*(COLUMN(B2:I2)<J2)),COLUMN(B2:I2)),0).
  4. 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. 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.
Find and combine populated row entries even when the row contains gaps.Open and save Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface with formula and fill-handle tools.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.