Excel Formula to Concatenate Values While Ignoring Zeros
Question details
Create a concatenation formula that returns a blank cell when the source cells are blank or contain zero values, including zeros hidden by number formatting.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user combines values from source cells for downstream processing and needs to prevent empty source rows from being imported as data.
- Observed behavior
- Hidden zero values are still numeric zeros, so a normal concatenation formula can return an apparent result instead of a truly blank cell.
Identify the source cells and decide whether a result should be blank only when every source cell is blank or zero. Remember that custom number formatting can hide a zero visually, but the cell still contains the value 0.
Test source cells before concatenating their values
Use this formula when the result should remain blank only if both source cells are blank or equal to zero.
The formula checks each source cell for either an empty value or a numeric zero before it joins the values. Replace A2 and B2 with the cells used in your worksheet, and add a separator if your output requires one.
Click the cell where you want the combined value to appear, such as C2.
Type =IF(AND(OR(A2="",A2=0),OR(B2="",B2=0)),"",A2&B2) and press Enter.
To place a space between nonempty values, change the final part of the formula to A2&" "&B2.
Drag the fill handle from the output cell down the column to apply the formula to additional rows.

Use TEXTJOIN to ignore blank cells in newer Excel versions
Use this option when you need to join several cells and want blank cells ignored, while separately preventing all-zero rows from producing output.
Concatenate values and suppress zero-only rows in WPS Spreadsheets
WPS Spreadsheets supports familiar Excel-compatible IF, AND, OR, COUNTIF, and TEXTJOIN formulas. Enter a conditional concatenation formula to keep output cells blank when a source row contains only blanks or zero values.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your .xlsx or .xls workbook.
- 2. Choose the output column: Click the first destination cell where the concatenated value should appear.
- 3. Enter the conditional formula: Enter =IF(AND(OR(A2="",A2=0),OR(B2="",B2=0)),"",A2&B2) and press Enter.
- 4. Copy the formula to remaining rows: Drag the fill handle down from the selected result cell to apply the formula to the rest of the data.

Frequently Asked Questions
Why does a concatenation formula return a value when zeros are hidden?
A custom number format hides the display of 0 but does not remove the numeric value. Test the cells with A2=0 or use a conditional IF formula before concatenating.
How do I leave the result blank when either source cell is zero?
Use OR instead of AND for the outer test. For example: =IF(OR(A2="",A2=0,B2="",B2=0),"",A2&B2). This returns blank when either source cell is blank or zero.
Can I add a separator between concatenated values without leaving extra spaces?
Yes. Use a conditional separator, such as =IF(AND(OR(A2="",A2=0),OR(B2="",B2=0)),"",A2&" "&B2). For more complex lists, use TEXTJOIN with its ignore-empty argument set to TRUE.
Does the formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports Excel-style IF, AND, OR, COUNTIF, concatenation with &, and TEXTJOIN formulas in compatible .xlsx and .xls workbooks.




