Get the First Nonblank Value in a List

To get the first nonblank value in a list, scan the items from the beginning and return the first one that is not empty or whitespace. In a spreadsheet, use a formula such as INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>"",0),0)); in code, loop through the list and stop at the first nonblank item.

What “Nonblank” Means

The key is deciding what counts as blank. A truly empty cell or an empty string ("") is usually blank, but a cell containing spaces is not technically empty. A value such as 0 or FALSE is also not blank and should generally be returned if it appears first.

For lists of text, whitespace-only entries are often intended to count as blank. For mixed lists, however, avoid treating every value that looks false-like as empty: zero may be a legitimate measurement, and FALSE may be a meaningful result.

• Empty: no value is present.
• Empty text: a formula or program returns "".
• Whitespace: one or more spaces, tabs, or other spacing characters.
• Zero or false: actual values, not blanks.

Find the First Nonblank Value in a Spreadsheet

If your values are in cells A1 through A10, this formula finds the first cell that is not an empty string and returns its value:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>"",0),0))

The inner comparison checks each cell against "". MATCH finds the position of the first TRUE result, and INDEX returns the value at that position. This form works in many spreadsheet versions without requiring a special key combination. In current Excel versions, an alternative is =XLOOKUP(TRUE,A1:A10<>"",A1:A10,"No nonblank value").

Google Sheets also supports a compact approach for a single column: =IFNA(INDEX(FILTER(A1:A10,A1:A10<>""),1),"No nonblank value"). The FILTER portion keeps nonempty entries, and INDEX selects the first remaining one. Choose the formula that fits the spreadsheet application and version you use.

Ignore Cells That Contain Only Spaces

A comparison with "" does not regard a cell containing spaces as blank. To treat ordinary leading and trailing spaces as blank, test the trimmed values instead. In Excel, for example:

=INDEX(A1:A10,MATCH(TRUE,INDEX(TRIM(A1:A10)<>"",0),0))

This checks whether each value has anything left after ordinary spaces are removed. If the first nonblank result should also be returned without its extra spaces, use a formula that returns the cleaned values, such as =INDEX(TRIM(A1:A10),MATCH(TRUE,INDEX(TRIM(A1:A10)<>"",0),0)), subject to the array behavior supported by your spreadsheet version.

Trimming may not remove every invisible character. Data copied from websites or imported from files can include nonbreaking spaces and other characters that look like ordinary spaces. If a cell appears blank but the formula skips it, inspect or clean the source data; replacing those characters may be necessary before applying a whitespace test.

Get the First Nonblank Value in Code

The same rule works in most programming languages: examine values in order, apply the intended blank test, and return as soon as one passes. For example, in Python, to skip empty strings and strings made only of whitespace:

first = next((item for item in items if item is not None and str(item).strip() != ""), None)

This returns the first qualifying item, or None if none exists. Note that converting values to strings makes the test treat numbers such as zero as nonblank because str(0).strip() is "0". If the list is guaranteed to contain strings or None, a simpler version is next((item for item in items if item and item.strip()), None), but that version treats other false-like values differently and is not suitable for arbitrary mixed data.

In JavaScript, where only nullish values and empty or whitespace-only strings should be skipped, use:

const first = items.find(x => x != null && (typeof x !== "string" || x.trim() !== ""));

Here, find returns the first matching item, or undefined when no item matches. This deliberately preserves zero and false.

Handle the Case Where Every Item Is Blank

A list can contain no qualifying value. Decide what the result should be rather than relying on an error or an ambiguous blank output. Common choices are a message such as “No nonblank value,” a null-like value, or a designated fallback.

In a spreadsheet, XLOOKUP can specify a not-found result directly, as shown above. For formulas using MATCH, wrap the expression in an error-handling function if a friendly fallback is needed, for example =IFNA(INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>"",0),0)),"No nonblank value"). In code, an explicit fallback makes downstream behavior easier to understand.

Common Mistakes and Choosing a Method

• Using a truthiness test: In some languages, zero and false count as false-like. A test such as if item may skip valid values.
• Checking only for empty text: This can leave whitespace-only entries in the results. Trim or normalize data if spaces should count as blank.
• Searching the wrong range: Make sure the range follows the intended order. The “first” value is determined by list order, not by value size.
• Forgetting the no-match case: Decide on a fallback for a completely blank list.
• Changing the data type: A formula or code conversion can turn numbers into text. Preserve the original value if its type matters.

For a short spreadsheet range, a formula is convenient and updates as the cells change. For reusable data processing, a loop or a built-in search function is usually clearer. If the list is large or contains imported text, clean and define the blank rule first; otherwise, a technically correct search may not match what you consider empty.

Frequently Asked Questions

How do I get the first nonblank cell in Excel?
Use =INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>"",0),0)), replacing the range with yours. Add error handling if the range might be entirely blank.

Does a formula returning an empty string count as blank?
For a test against "", yes: a formula result of "" is treated as empty text. A cell containing a space is different.

How can I skip cells that contain spaces?
Test a trimmed version of each value, such as TRIM(A1:A10)<>"". Nonbreaking spaces and other invisible characters may need separate cleaning.

Will the search skip zero?
It should not if zero is a valid value. Comparisons against an empty string in the spreadsheet formulas above preserve zero; in code, avoid a general truthiness test when zero matters.

What happens if every item is blank?
A search may return an error, null-like value, or no result depending on the tool. Set an explicit fallback, such as a message or a null value.

Related

• Excel Formula to Subtract Minutes From a Time
• Real Size Magnification: Formula and How to Calculate It
• Temperature Conversion List: Celsius, Fahrenheit and Kelvin