Solved - Google Sheets QUERY returns null values for mixed-data columns despite forcing TO_TEXT inside an ARRAYFORMULA wrapper

📌 The Problem Explained

Imagine you are a data analyst or project manager consolidating a massive report in Google Sheets. You have a column for “Serial Numbers” or “Invoice IDs” where some entries are strictly numeric (e.g., 100234) and others are alphanumeric (e.g., INV-100235).

When you use the QUERY function to filter this data, Google Sheets performs a “majority rule” check on each column’s data type. If the majority of your rows are numbers, the QUERY engine treats the entire column as numeric. Consequently, any cell containing text is returned as a null (blank) value.

Even when users try to wrap the range in ARRAYFORMULA(TO_TEXT(A:C)), the QUERY function sometimes still fails because of how it parses the data headers or internal caching. This creates a massive headache for automated reporting and data cleaning workflows.

💡 The Quick Solution

The most reliable way to force Google Sheets to treat every cell as text—thereby bypassing the data-type detection—is to concatenate the entire range with an empty string within the QUERY range argument.

Copy and paste this formula structure:

=QUERY(ARRAYFORMULA(DataSheet!A:C & ""), "SELECT * WHERE Col1 IS NOT NULL", 1)

Key Note: When you wrap your range in a function like ARRAYFORMULA, you must refer to columns as Col1, Col2, Col3, etc., instead of A, B, or C.

🛠️ Step-by-Step Breakdown

Follow this table to understand how to reconstruct your formula to prevent null values in mixed-data columns.

Step	Component	Description
1. Define Range	DataSheet!A:C	Select the source data containing the mixed values.
2. Force String	& ""	Adding an empty string to every cell forces Google Sheets to interpret the entire array as text.
3. Wrap Array	ARRAYFORMULA(...)	This ensures the concatenation (& "") happens for every single row in the range.
4. Apply Query	QUERY(..., "SELECT...")	Run your filter or sort on the now-sanitized text-only data.
5. Column Ref	Col1, Col2	Since the range is now “computed,” standard column letters won’t work; use index-based references.

Implementation Workflow:

1. Identify the sheet and range containing your mixed data.
2. In your destination cell, start your formula with =QUERY(ARRAYFORMULA(.
3. Add your range followed by & "".
4. Close the ARRAYFORMULA parenthesis.
5. Write your query string (e.g., "SELECT Col1, Col2 WHERE Col3 > 0").
6. Set your header argument (usually 1) and hit Enter.

⚠️ Common Mistakes to Avoid

• Using Column Letters: A very common pitfall is writing "SELECT A, B". Because the ARRAYFORMULA(TO_TEXT()) or & "" creates a virtual array, the QUERY engine no longer recognizes the original column letters. You must use Col1, Col2, etc.
• Header Misalignment: If you use TO_TEXT or & "" on the entire range including headers, the QUERY function might treat your header row as data. Always ensure you specify the [headers] argument at the end of the QUERY function (usually , 1) to tell the engine where your data actually starts.
• Formatting vs. Data Type: Changing the format of the column to Format > Number > Plain Text via the UI often does not fix the QUERY issue, as the underlying data type remains mixed. The formula-based approach is the only foolproof method for automation.