How to Combine Nonblank Values from Multiple Excel Columns
Question details
The user wants to return a single populated value from multiple source columns in a row without including unwanted blanks.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data across multiple columns where only one column per row contains the desired target value.
- Observed behavior
- The user needs a formula to locate and extract the nonblank value without incorrectly summing numbers or merging blank cells.
Verify that your target columns contain only one expected value per row to avoid accidental merging, and ensure your spreadsheet software supports dynamic array formulas like XLOOKUP or TEXTJOIN.
Use the XLOOKUP Formula to Return the First Nonblank Value
XLOOKUP is the most robust method for extracting exactly one populated value from a range without concatenating strings.
This approach is highly recommended when you are certain that only one source cell in the row contains the answer you need. It searches for the first cell that is not empty and returns its value.
Click on the empty cell where you want the nonblank value to be displayed.
Enter the formula =XLOOKUP(TRUE, D2:H2<>"", D2:H2, ""), making sure to replace D2:H2 with your actual data range.
Press Enter to apply the formula, then click and drag the fill handle at the bottom-right corner of the cell down to apply it to the rest of your dataset.
Use TEXTJOIN or CONCAT Formulas to Ignore Blanks
If multiple columns might have data or you are working strictly with text, TEXTJOIN or CONCAT will combine them while ignoring empty cells.
Use SUM or MAX Formulas for Numeric Data
If your data consists exclusively of numbers or dates, basic math functions can easily return the single nonblank value.
Combine Column Data Effortlessly with WPS Spreadsheet
WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced functions like XLOOKUP, TEXTJOIN, and CONCAT to help you manipulate and clean your data effortlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to combine.
- 2. Select your target cell: Click the empty cell in the column where you want the nonblank value to appear.
- 3. Apply the formula: Enter =XLOOKUP(TRUE, D2:H2<>"", D2:H2, "") or your preferred text formula and press Enter.
- 4. Fill the column: Use the fill handle on the bottom-right of the cell to drag the formula down for all rows.
Frequently Asked Questions
Why does XLOOKUP return an error if all cells are blank?
If no nonblank cell is found, XLOOKUP might return an #N/A error. You can prevent this by adding a final argument to the formula, like =XLOOKUP(TRUE, D2:H2<>"", D2:H2, "No Data"), to display a custom message or a blank space instead of an error.
Can I combine nonblank values if I have older spreadsheet software?
Yes. If functions like XLOOKUP or TEXTJOIN are not available in your older version, you can use the ampersand (&) operator, such as =D2&E2&F2&G2&H2. This will string together the text and naturally ignore the blanks.
How do I add a comma between values if multiple columns are nonblank?
You should use the TEXTJOIN function. The formula =TEXTJOIN(", ", TRUE, D2:H2) uses a comma and a space as the delimiter and ignores any blank cells in the range, formatting multiple values cleanly.
