Google-sheets – QUERY data based on Alpha Range in Google Sheets

formulasgoogle sheets

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!

Best Answer

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.

Related Topic