0

I have a sheet that contains all orders from an eCommerce site. I made a new tab within that sheet to only bring the necessary columns to process orders (name, address, sku...).

The query is this: =QUERY(raw_data!A:GM, "select AY, AT, EX, EZ where FB contains 'Product's Name' and AT=''",0)

The issue is that when the ZIP code starts with 0 or a letter, it is not imported and its cell remains empty.

Photo Example

I believe the issue comes from the columns having multiple types of data (numbers, strings).

How can I solve this to bring all types of ZIP codes?

Thanks everyone!

PerplexingParadox
  • 1,196
  • 1
  • 7
  • 26
the_avalanche
  • 3
  • 1
  • 1
    Can you share a copy of the spreadsheet with dummy data? Also, have you considered using the solution of changing the format of the column? – Kessy Apr 15 '21 at 13:16

2 Answers2

0

Try

Query does indeed have issues with mixed data types. One way to solve that is to convert everything to text..

=QUERY(INDEX(raw_data!A:GM&""), ..

As a consequence of that, you'll have to use numeric column references:

=QUERY(INDEX(raw_data!A:GM&""), "Select Col51, Col46 ..."

Hope that helps?

JPV
  • 26,499
  • 4
  • 33
  • 48
0

A simple approach is to format the column(s) as text

enter image description here

In this example, the first column is not formatted and drops the leading zero, col B is formatted as text

enter image description here

JohnA
  • 1,058
  • 6
  • 12