Function Problems

Combine Multiple Excel Ranges in One Cell Without Spilling in WPS Spreadsheets

Muhammad Talha Nov 13, 2026 871 views

Question details

The user wants to combine nonblank values from multiple Excel ranges into a single cell without triggering a dynamic array spill.

Combine Multiple Excel Ranges in One Cell Without Spilling in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: Creating a summary where values from several non-contiguous ranges need to be joined into one cell while ignoring empty cells.
Observed behavior: A single cell should contain the joined values as one text result instead of spilling into multiple cells.

Before you start

Make sure you are using WPS Office Spreadsheets with TEXTJOIN support. Identify the ranges you want to combine and the delimiter, such as a comma or space.

Solution 1Recommended

Use TEXTJOIN to Combine Multiple Ranges in a Single Cell

Use this method when you need a single joined text value from several non-contiguous ranges and want blank cells ignored automatically.

TEXTJOIN supports multiple range arguments in WPS Spreadsheets. The second argument TRUE tells the formula to skip empty cells, which prevents extra delimiters and avoids a dynamic array spill.

Open the workbook in WPS Spreadsheets

Launch WPS Office, open the workbook, and select the worksheet that contains the ranges you want to combine.

Select the target cell

Click the cell where you want the combined result to appear, such as a summary cell outside the source ranges.

Enter the TEXTJOIN formula

Type =TEXTJOIN(",",TRUE,D36:D63,D66:D68,D71:D72) and replace the ranges with your own non-contiguous ranges.

Press Enter

Press Enter to complete the formula. The cell displays all nonblank values from the listed ranges joined by commas in one cell.

Use TEXTJOIN to Combine Multiple Ranges in a Single Cell

Result: Because TRUE ignores empty cells, the formula returns one combined text string and does not spill into adjacent cells.

WPS Spreadsheets formula

Combine Excel Ranges in One Cell with WPS Spreadsheets TEXTJOIN

Use TEXTJOIN in WPS Spreadsheets to join nonblank values from multiple ranges into a single cell without spilling. Set the second argument to TRUE to ignore blanks and enter the desired delimiter.

1. Download WPS Office: Download WPS Office Free from the official website and install WPS Spreadsheets.
2. Open your Excel workbook: Open the .xlsx or .xls file in WPS Spreadsheets.
3. Select the result cell: Click the cell where you want the joined result to appear.
4. Enter the TEXTJOIN formula: Type =TEXTJOIN(",",TRUE,D36:D63,D66:D68,D71:D72) and adjust the range arguments to match your worksheet.
5. Press Enter: Press Enter to get one joined value in the selected cell.

Combines non-contiguous ranges into one cell without dynamic array spillsIgnores blank cells automatically for cleaner resultsSupports .xlsx, .xls, and Microsoft Excel formula compatibilityFree access to TEXTJOIN and other spreadsheet functions

microsoft office alternative - wps office

Frequently Asked Questions

How do I include only nonblank cells when combining Excel ranges?

Use TEXTJOIN with TRUE as the second argument, for example =TEXTJOIN(",",TRUE,D36:D63,D66:D68,D71:D72). This skips empty cells and returns one joined result.

Why does my formula spill into multiple cells?

Spilling happens when a formula returns an array. TEXTJOIN with multiple ranges returns a single text string, so place the formula in one cell and use TRUE to ignore blanks.

Can I combine ranges from different worksheets in WPS Spreadsheets?

Yes. Reference each sheet range as a separate argument, such as =TEXTJOIN(",",TRUE,Sheet2!A1:A10,Sheet3!B2:B20).

Which delimiter should I use in TEXTJOIN?

Set the first argument of TEXTJOIN to your preferred delimiter, such as a comma ",", a space " ", or an empty string "" for no separator.

Excel & Data Problems