'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Blank Cells Displaying as 0 in Concatenation Formulas
Question details
The user needs to prevent concatenation formulas from returning '0 - 0' when referencing cells that appear to be blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple cells where some of the referenced source cells are empty or contain hidden characters.
- Observed behavior
- The concatenation formula outputs zeroes (e.g., '0 - 0') instead of leaving the target cell blank.
Before modifying your formulas, click on the source cells and check the formula bar to ensure they are truly empty, without hidden spaces, apostrophes, or invisible characters.
Use a Conditional IF and OR Formula
Wrap your concatenation in a conditional statement to ensure the formula only runs if both referenced cells contain actual data.
Click on the cell where you want the final concatenated text to appear.
Type the formula =IF(OR(A1="",B1=""),"",A1&" - "&B1) into the formula bar. Replace A1 and B1 with your actual cell references.
Press Enter. The cell will now remain completely blank if either source cell is empty, avoiding the '0 - 0' output.
Verify and Clean Source Cells
If conditional formulas still return 0, the source cells likely contain invisible characters or formulas returning empty strings instead of being truly blank.
Use IF with the CONCAT Function
Check for non-zero values explicitly when using the CONCAT function to bypass default zero displays.
Handle Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet perfectly supports standard Excel functions like IF, OR, and CONCAT, making it incredibly easy to manage empty cells and avoid formatting errors.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your concatenation formulas.
- 2. Apply the conditional formula: Select your target cell and enter =IF(OR(A1="",B1=""),"",A1&" - "&B1).
- 3. Copy the formula down: Drag the fill handle down to apply the clean concatenation to the rest of your data without generating '0 - 0' errors.
Frequently Asked Questions
Why does Excel show a 0 when referencing a blank cell?
Excel often evaluates completely blank cells as numerical zeroes when they are referenced inside text or math formulas. This causes concatenation operators to output '0' instead of leaving the space blank.
What is the difference between ISBLANK() and checking for an empty string ("")?
The ISBLANK() function only returns TRUE if a cell is absolutely empty. If a cell contains a formula that outputs an empty string, ISBLANK() sees it as non-empty, whereas checking A1="" will accurately recognize the empty string.
Can I hide all zero values in my worksheet instead of changing formulas?
Yes, you can hide zero values globally by going to File > Options > Advanced, scrolling to 'Display options for this worksheet', and unchecking 'Show a zero in cells that have zero value'. However, adjusting the specific conditional formula is much safer for targeted data formatting.
