Google-sheets – Sort QUERY results to the 4th character of a string

formulasgoogle sheetsgoogle-sheets-queryregexworksheet-function

I'm trying to pull a list of people based on their last names. The catch is I only want to QUERY people with last names that fit between an alpha range to the 4th character.

Example

I only want names that fall between Mimz and Sand

I have this formula, but it doesn't go to the 4th character and I don't understand how to update. 

=QUERY(data!B1:J30000, "Select B, C, D, E, F, G, H, I, J  where lower(D) matches 
'^(m[k-z]|[n-r]|sa).*' order by D")

I believe the adjustment needs to be made here (m[k-z]|[n-r]|sa) but I don't know the syntax.

Any help is greatly appreciated.

Link to copy of report tab

https://docs.google.com/spreadsheets/d/1rL0ufamiMfC6qZhalsPw9s0SuKuBdXuNGVx_JtoOSEc/edit#gid=1728510345

Link to copy of data tab (simplified)

https://docs.google.com/spreadsheets/d/1rL0ufamiMfC6qZhalsPw9s0SuKuBdXuNGVx_JtoOSEc/edit#gid=2033321539

Best Answer

Since this is more a regular expression logic update, I can contribute:

Try:

'^(mimz|mi[n-z]|m[j-z]|[n-r]|sa[a-m]|san[a-d]).*'

Just keep in mind that the range above needs to differ by the first character. If not, then pull the common characters out. For mimz-mitz:

'^mi(mz|[n-s]|t[a-z]).*'

Not saying there isn't an easier way -- this just helps you update this particular way in the future.

Related Topic