Function Problems

How to Return the Only Non-Blank Value from Two Excel Cells

Natalie Taylor Sep 25, 2026 869 views

Question details

The user needs to formulate a cell to return the only non-blank value from two other cells, which may output either a date or a number, requiring consistent formatting for both data types.

How to Return the Only Non-Blank Value from Two Excel Cells

Product: Excel
Device & OS: not provided
Scenario: Pulling data from two source cells where one returns a blank and the other returns either a numeric value or a date, and displaying the non-blank result accurately in a third destination cell.
Observed behavior: Standard formulas like MAX may successfully return a date but fail for general numbers, and basic IF statements leave the cell blank or format the returned value incorrectly without custom rules.

Before you start

Verify whether your source cells return true blank strings ("") or zero values, as this will determine the exact logical condition required for your IF formula.

Solution 1Recommended

Use an IF Formula with Custom Number Formatting

This recommended approach combines conditional logic to isolate the non-blank value with a custom number format that correctly displays both small integers and larger date serial numbers.

Because dates in spreadsheets are stored as large serial numbers (e.g., 40000+), extracting both standard numbers and dates into a single cell requires a specialized number format. This ensures standard numbers remain numbers, while date serials are converted into a readable calendar format.

Enter the conditional IF formula

Select your destination cell and enter an IF formula that evaluates your controlling value. For example, type =IF(A10="Number",1,'Register'!AK33+1) or simply =IF(A1="", B1, A1) depending on your spreadsheet structure, and press Enter.

Open the Format Cells dialog

Right-click the destination cell containing your new formula and choose 'Format Cells' from the context menu, or use the keyboard shortcut Ctrl + 1.

Apply a custom date and number format

Navigate to the 'Number' tab and select 'Custom' from the category list. In the 'Type' field, input [<36000]General;mm/dd/yyyy and click OK. This forces values below 36,000 to display as standard numbers and larger values to format as dates.

Use an IF Formula with Custom Number Formatting

Test Your Results: Change the values in your source cells to test both possible outcomes. Confirm that the destination cell accurately outputs a standard number or a formatted date without remaining blank.

Solution 2

Use the MAX Function for Homogeneous Data

If you are only dealing with one type of data (only numbers or only dates), the MAX function can easily ignore blank cells without complex logic.

Advanced Formulas Made Easy

Handle Complex Conditional Formulas in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, allowing you to seamlessly execute advanced IF functions, manage custom number formats, and extract non-blank cell values with ease.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the document containing your blank and non-blank target cells.
2. Input your conditional logic: Click the destination cell and enter your IF formula to evaluate the two source cells.
3. Apply the custom format: Right-click the cell, select 'Format Cells', choose 'Custom', and enter [<36000]General;mm/dd/yyyy to correctly display both numbers and dates.

100% compatible with Microsoft Excel formulas, including IF, MAX, and ISBLANKFull support for advanced custom number formatting rulesLightweight, fast, and completely free to useFamiliar user interface for seamless workflow transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does my extracted date appear as a 5-digit number?

Spreadsheet software stores dates as sequential serial numbers for calculation purposes. To fix this, you must change the cell's formatting from 'General' to 'Short Date' or apply a custom date format.

How can I check if a cell contains a true blank in my formula?

You can use the ISBLANK(A1) function to verify if a cell is completely empty. However, if the cell contains a formula that returns an empty string (""), ISBLANK will return FALSE. In that case, use A1="" as your logical test.

Can I use the CONCATENATE function to merge the two cells instead?

While combining cells with CONCATENATE or the '&' operator will visually bypass the blank cell, it converts the output into a text string. This strips away any underlying date formatting, turning dates into unformatted serial numbers.

Excel & Data Problems