Solved Google Sheets QUERY returns null values when wrapping ARRAYFORMULA with REGEXEXTRACT for mixed alphanumeric columns

📌 The Problem Explained

Data analysts and office professionals often use a combination of ARRAYFORMULA and REGEXEXTRACT to clean messy data, such as pulling SKU numbers or ID codes from long text strings. However, a common frustration occurs when you wrap that entire result in a QUERY function: the data appears in the sheet, but the QUERY returns null (blank) values for specific rows.

This happens because the QUERY function in Google Sheets is strictly typed. It scans the first few rows of a column to determine if the data is “Numeric” or “Text.” If REGEXEXTRACT returns a mix—where some extracted values look like numbers (e.g., “101”) and others contain letters (e.g., “101A”)—the QUERY engine gets confused. It chooses one data type and treats everything else as null, effectively breaking your automation and data cleaning workflow.

💡 The Quick Solution

To fix this, you must force all extracted values into a String format before the QUERY function processes them. The most efficient way to do this is by appending an empty string (& "") to your range inside the formula.

Use this optimized formula structure:

=QUERY(ARRAYFORMULA(REGEXEXTRACT(A2:A, "(\d+[A-Z]+)") & ""), "select * where Col1 is not null")

By adding & "" at the end of the extraction logic, you coerce every result into a text format, ensuring the QUERY function sees a uniform data type.

🛠️ Step-by-Step Breakdown

Follow this logic to build a robust, error-free formula for mixed alphanumeric data.

Step	Action	Description
1	Identify Range	Select the column containing your raw alphanumeric strings (e.g., A2:A).
2	Apply REGEX	Use REGEXEXTRACT(A2:A, "pattern") to pull the specific data you need.
3	Coerce to Text	Add & "" immediately after the REGEXEXTRACT function. This is the “magic” step that prevents null values.
4	Wrap ARRAYFORMULA	Surround the logic with ARRAYFORMULA() so it processes the entire column at once.
5	Apply QUERY	Wrap the result in QUERY({range}, "select *"). Use Col1 notation if you are wrapping an array.

The Workflow Implementation

1. Open your spreadsheet and navigate to the cell where you want the results.
2. Input your formula, ensuring you use curly braces { } if you are combining multiple columns.
3. Format the output by going to Format > Number > Plain Text to ensure visual consistency.

⚠️ Common Mistakes to Avoid

• Mixed Data Types: The most common error is leaving the data as “Automatic.” Even if you don’t use a formula, having a column that is 80% numbers and 20% text will cause QUERY to return nulls for the 20%. Always use the & "" trick or TO_TEXT() to unify the column.
• Wrong Column References: When you wrap a formula inside a QUERY, you cannot use lettered columns (like SELECT A). You must use the Col notation (like SELECT Col1, Col2) because the data is now part of an internal array rather than a direct sheet reference.
• Header Confusion: If your QUERY results are shifting or missing the first row, check your header argument. Ensure you specify ,1 or ,0 at the end of the query: =QUERY(range, "query", 1).

By mastering this google-sheets troubleshooting technique, you can build more resilient data pipelines and avoid the “missing data” headaches common in complex spreadsheet automation.