I'm trying to figure out if I can pull data from a sheet in a Google Sheets Workbook based on an alpha range.

For example. I only want data associated with 'last name' that start 'A' and goes through 'F' to be QUERIED and copied to the A-F Sheet.

I assume I'll use part of the following, but I don't know how to adjust the section contains 'Day 1' so it only selects an alpha range.

=QUERY(responses!A1:I, "Select B, C, D, E, F, G, H, I where B contains 'Day 1' ")

Example Google Sheet

Any help or direction will be greatly appreciated!

1 Answer 1


The where clause supports several types of string matching.

Method 1: String comparison with >= and <

The query string

select A where A < 'G'

selects all strings that would precede G in a dictionary, i.e., all that begin with letters A-F. This is case-sensitive. The case-insensitive form is

select A where lower(A) < 'g'

More complex examples

select A where lower(A)<'cp' 

selects in the range a-co.

select A where lower(A) >= 'cp' and lower(A) < 'hb'

selects the range cp-ha.

Method 2: regular expressions

The query string

where B matches '^[A-F].*'

selects the rows where the content of B begins with a letter from A to F. (The regular expression means: ^ beginning of line, [A-F] one character in this range, .* other characters may follow.)

The match is case-sensitive. Its case-insensitive version would be

where lower(B) matches '^[a-f].*'

More complex examples

Matching strings in alphabetic range "a" to "co".

where lower(B) matches '^([ab]|c[a-o]).*'

Here | separates alternatives. Beginning with a or b is okay. So is beginning with c, but only if followed by a letter in range a-o.

Matching the range cp-ha:

where lower(B) matches '^(c[p-z]|[d-g]|ha).*'

There are three alternatives here: c followed by p-z, anything beginning with d-g , and ha.

  • Awesome, THANK YOU! What if I needed to QUERY by a-fm instead of a-f, is that possible?
    – Mr. B
    Jan 14, 2016 at 17:44
  • What if I want the following tabs. Tab A = a-co and Tab B = cp-ha? Last name = Coleman would be on Tab A. Last name = Crawford would be on Tab B. Is that possible?
    – Mr. B
    Jan 14, 2016 at 18:08
  • 2
    Added to the answer.
    – user79865
    Jan 14, 2016 at 18:15
  • 1
    Actually, I overcomplicated things: see the simpler "Method 1".
    – user79865
    Jan 14, 2016 at 20:36
  • 1
    Yes, add order by B at the end. Please see this page which explains the use of all language clauses.
    – user79865
    Jan 15, 2016 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.