Google Sheets QUERY Function Tutorial - Advanced Contains with Matches & Regular Expression - Part 2
Вставка
- Опубліковано 13 лип 2017
- Video tutorial series about QUERY function in Google Sheets.
In this tutorial we'll cover how to use QUERY function in Google Sheets to pull filtered results out of your main dataset using regular expression.
Google Sheets
www.google.com/sheets/about/
Website:
www.chicagocomputerclasses.com/
ahhh thank you, you saved me so much work. Blessings on you and your extended family!
You are a great teacher.
Thank You +Learn Google Spreadsheets for this series!
Very Nice Thank you sir.
Thank you....❤
good
Fuck me! That was exactly what i was looking for. I wrote part of this function in GAS.. damn... well i actually learned some JS along the way...
Great videos, good quality, keep it up!
🙏🏻
great videos, been interested in learning more about query functions for my sheets, specifically referencing a cell from a second sheet as part of the filter inside the query like this: =query(INPUT!A2:C, "select * where C contains" Timesheet!B7, 0) curious if you had any advice, tried a couple of things, do i need to wrap it in another query?
try "select * where C contains '" & Timesheet!B7 & "'"
Learn Google Spreadsheets it's always the simple things, works great! Thank you
Had a quick question on Query ....I have data in which one column has number format as " AB12234" when I use query function it's gives put as " " can you please provide resolution for this .... thank you
how do you transfer a row of data to another tab based on a dropdown selection?
This is a great video and I've been playing around with this to great effect. However I have set myself a problem that I can't work out and wonder if you know the solution or might make a tutorial of it?
PROBLEM:
Imagine you have a very messy web-scraped list of data in 100 rows of Column A where say the first 10 rows are junk data and the 11th line starts with the data you want "GoodDataStartsHere". How do you get the regex inside a query to identify the start of the good data and ignore any data before it? Similarly if there is junk data at the end of a list how to identify the last good bit of data "GoodDataEndsHere" and ignore anything after it? Appreciate any responses to this.
Depends on your data. What's in junk data that's not in good data?
How do I "select" only columns that contain text and not empty containers?
can you provide the data in which you are teaching. it will be a great help for pracitce.
How can i use NOT LIKE? Have a solution for that? Because doesn't work not like in query... have any solution for that?
Great videos! Learning a lot! How can I run a query such as =QUERY(E22:I42,"select E,F,G where E < $K$15",0)? Can you reference the value of another cell in the query? If so, can you direct me to the video that explains this? Thanks! ~Ben
=QUERY(E22:I42,"select E,F,G where E < " & $K$15,0)
How to write if there are more than 2 output of "LIKE" is needed.i.e you had written select e,f,d,h,c where c like %western%...What if I want LIKE %western% and %eastern%
select e,f,d,h,c where c like %western% or c like %eastern%
Hi Sir! Your tutorials are perfect for my need. By the way Sir, I have a problem, and I hope for your kind reply.
My selection is below.
select A, B, C, D, E, F, G WHERE G matches '.*[sS]ample [oO]only'
Please teach me how can I add ALL CAPSLOCK words? Because I could only see Big and Small letters..
I really need your kind help Sir.
Thank you so much for helping many people like me.
Please provide a sample data with results needed out of the sample, otherwise it's difficult to understand what you need,
Wow! Thank you so much for quick reply Sir. Please see the Gsheet URL below. I hope to include also ALL CAPS-LOCK words in Column G.
docs.google.com/spreadsheets/d/1MW-wH8FQ9e4fwc5vhJSrExZveveSslXEXgXM6mF9S30/edit?usp=sharing
Please HELP me Sir, what formula is perfect for my need.
Thank you so much for your great help in advance!
select A, B, C, D, E, F, G WHERE LOWER(G) = 'sample only'
WOW!!! PERFECT!!! Sir, Thank you very, very much from the bottom of my heart! Everything is perfect now ❤ ❤ ❤ GOD bless you with good health and great protection always!
What if I like the query to match a specifc data sets from a different sheet?
I have a video in thee series that covers how to do SQL IN type of statements. I believe that should help.
Can we do exclude for text in query functions? Say for example i want to query all states Except CA
WHERE D != 'CA'
@@ExcelGoogleSheets Thank you so much! Btw how do I remove GRAND TOTAL from the pivot table?
Its ok I found out i just need to uncheck the text box that 'shows total'
I am trying to match 'Ma'an' but it is not working :( Any tips on how to fix this?
Laza Lazarevic try to use double quotes. It prolly doesnt work cause it reads it as 'Man' and the rest is just error.
Javascript has a way to allow you to use quotes in strings by writing a / in front of it. Search or try smtg similar.
hi guys , who can share with me in Transactions data file?
I have been having problems with the query, Because I get the data from a Software and then I pass the data to Google sheets in the same column I have Text and Numbers, the query just pass the Number values and the Text values left them blank. why is that? how can I Fixt that without having to do format?
QUERY doesn't support mixed data types in a column. IF you want to keep mixed types use FILTER.
@@ExcelGoogleSheets Is there a way to clean up the data, like if I want that Col 2 is all text..... the values in Col2 should be Text but GoogleSheets take some as numbers and that break all my plan
You should be able to do some sort of array {A1:A,TEXT(B1:B,"@"),C1:C}