Function Problems

Excel Formula to Concatenate Values While Ignoring Zeros

Anum MushtaqAnum Mushtaq Nov 12, 2026 868 views

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.

Excel Formula to Concatenate Values While Ignoring Zeros
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output cell

Click the cell where you want the combined value to appear, such as C2.

2
Enter the conditional concatenation formula

Type =IF(AND(OR(A2="",A2=0),OR(B2="",B2=0)),"",A2&B2) and press Enter.

3
Add a separator when needed

To place a space between nonempty values, change the final part of the formula to A2&" "&B2.

4
Fill the formula down

Drag the fill handle from the output cell down the column to apply the formula to additional rows.

Test source cells before concatenating their values
Why this works: The formula returns an empty string only when both A2 and B2 are blank or zero. If either cell contains another value, it concatenates the two source values.
Work with formulas in WPS Spreadsheets

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your .xlsx or .xls workbook.
  2. 2. Choose the output column: Click the first destination cell where the concatenated value should appear.
  3. 3. Enter the conditional formula: Enter =IF(AND(OR(A2="",A2=0),OR(B2="",B2=0)),"",A2&B2) and press Enter.
  4. 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.
Use IF, AND, OR, and TEXTJOIN to control concatenated results.Keep zero-only source rows out of downstream spreadsheet imports.Open and edit Microsoft Excel .xlsx and .xls files with formula compatibility.Use a lightweight, familiar spreadsheet interface for formula editing.
microsoft office alternative - wps office

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.