Fix Google Sheets QUERY Mixed Data Types Fast

The Google Sheets QUERY function infers column data types by majority vote, then casts minority-type cells to null before query execution. If a column has mostly numbers, any text cells become blank.

To fix this, wrap your data in an ARRAYFORMULA to force uniform text coercion before the QUERY runs:

Plaintext

=QUERY(
  {A1:D1; ARRAYFORMULA(IF(ISNUMBER(A2:D), TEXT(A2:D,"0"), A2:D))},
  "SELECT *",
  1
)

The ARRAYFORMULA(IF(ISNUMBER(...))) layer forces every cell in the source range to a uniform text type before the Visualization API performs its column scan, eliminating the null-coercion step entirely. The header row is restored by prepending A1:D1 using the array union operator { ; }.

Prerequisites

• Google Sheets (Any Version): This fix uses the native Visualization API query language. The syntax is not portable to Excel 365 or Notion formula blocks.
• Mixed-Type Source Data: Specifically, a column where some cells contain numeric values and others contain text strings (e.g., legacy alphanumeric IDs alongside auto-incremented integers).
• ARRAYFORMULA Support: Confirm your sheet is not in a protected range or a locked external import that blocks formula array expansion.

The Syntax Breakdown

• {A1:D1; ...} — The outer curly-brace array constructor stacks two arrays vertically. The first element (A1:D1) preserves the original header row as raw text. Without it, the transformed array has no header and QUERY defaults all column labels to Col1, Col2.
• ARRAYFORMULA(...) — Expands the inner IF formula across every cell simultaneously, returning a two-dimensional array instead of a single value.
• ISNUMBER(A2:D) — Returns TRUE for each cell that holds a numeric value. This is the type-detection gate that identifies which cells need coercion.
• TEXT(A2:D, "0") — Converts numeric cells to their string representations using format code "0", rendering integers without decimals. Substitute "YYYY-MM-DD" if the mixed column contains dates.
• A2:D (The IF False Branch) — Passes text cells through unmodified. Cells already containing strings reach the QUERY engine as-is.
• "SELECT *" — The query string. After coercion, all columns are uniformly text-typed, so the API no longer drops any rows.

Real-World Example: Freight Operations Shipment ID Normalization

A logistics operations manager at a regional 3PL company runs a weekly QUERY to pull all “Delivered” shipments for invoicing. The source sheet is a merged export from two warehouse management systems: a modern WMS that auto-generates integer IDs, and a legacy system still generating alphanumeric codes (FRT-XXXX).

Column A holds both types. The standard QUERY silently drops every legacy record—roughly 30% of weekly invoice volume.

Raw Data Table

Shipment ID	Route	Units	Status
10042	CHI-LAX	340	Delivered
FRT-0019	NYC-DAL	180	In Transit
10317	MIA-SEA	520	Delivered
WH-0004	BOS-PHX	90	Pending
10088	ATL-DEN	410	Delivered

The Formula Applied

Plaintext

=QUERY(
  {A1:D1; ARRAYFORMULA(IF(ISNUMBER(A2:D6), TEXT(A2:D6,"0"), A2:D6))},
  "SELECT * WHERE Col4 = 'Delivered'",
  1
)

Output Table

Shipment ID	Route	Units	Status
10042	CHI-LAX	340	Delivered
10317	MIA-SEA	520	Delivered
10088	ATL-DEN	410	Delivered

Every “Delivered” shipment—including those carrying alphanumeric IDs—now appears in the result set.

Common Related Errors & Fixes

• ORDER BY Sorts Lexicographically -> Cause: The coercion converts numeric columns to text, and QUERY‘s ORDER BY respects the new string type (meaning “9” appears larger than “520”). -> Fix: Apply the coercion only to the specific column causing the mixed-type drop. Wrap only the problematic column: {A1:D1; ARRAYFORMULA(IF(ISNUMBER(A2:A), TEXT(A2:A,"0"), A2:A)), B2:D} to leave numeric columns intact.
• Empty Cells Flip the Inference -> Cause: A column of invoice amounts that contains even one blank cell causes QUERY to infer the column as “mixed,” returning zero rows for numeric filters. -> Fix: Wrap the source range with IF(B2:B="", 0, B2:B) inside the array constructor to eliminate blank-type contamination at the source.

Key Takeaways

• The core fix is type homogenization. Wrapping your source array forces the Visualization API to see a single data type per column, eliminating silent row drops.
• Applying coercion to an entire dataset indiscriminately will break numeric filters. Scope the text conversion only to genuinely mixed columns.
• Performance impact is low for ranges under 50,000 cells but degrades beyond that threshold because ARRAYFORMULA evaluates synchronously on every recalculation.

Frequently Asked Questions

Why does Google Sheets QUERY silently drop rows instead of throwing an error?

The QUERY function delegates column type inference to the Google Visualization API, which was designed for chart data pipelines where silently nulling incompatible values is preferable to halting execution. There is no native parameter to force the API to throw a type mismatch warning.

Does the ARRAYFORMULA TEXT coercion work in Excel 365?

No. Excel 365 does not use the Visualization API. Its equivalent is Power Query, which handles mixed-type columns with explicit type-cast steps in the M language editor rather than inline formula coercion.

What happens to date values when ISNUMBER is used in the coercion formula?

Google Sheets stores dates as serial floats internally, so ISNUMBER returns TRUE for date cells, and TEXT(date_cell, "0") converts them to their raw numeric serial number. Replace the format code with "YYYY-MM-DD" to produce ISO-formatted strings that remain filterable as text inside the QUERY.