4

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!

2

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.

| improve this answer | |
  • Awesome, THANK YOU! What if I needed to QUERY by a-fm instead of a-f, is that possible? – Mr. B Jan 14 '16 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 '16 at 18:08
  • 2
    Added to the answer. – user79865 Jan 14 '16 at 18:15
  • 1
    Actually, I overcomplicated things: see the simpler "Method 1". – user79865 Jan 14 '16 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 '16 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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