You can use the % sign as a wildcard character in Google Sheets queries:
Method 1: Return Cells That Start with Certain Characters
=QUERY(A1:A10, "Select A where A like 'hello%'")
Method 2: Return Cells That End with Certain Characters
=QUERY(A1:A10, "Select A where A like '%hello'")
Method 3: Return Cells That Contain Certain Characters
=QUERY(A1:A10, "Select A where A like '%hello%'")
The following examples show how to use each method in practice.
Method 1: Return Cells That Start with Certain Characters
We can use the following formula to return every cell in column A that starts with ‘We’:
=QUERY(A1:A10, "Select A where A like 'We%'")
The following screenshot shows how to use this formula:
Method 2: Return Cells That End with Certain Characters
We can use the following formula to return every cell in column A that ends with ‘th’:
=QUERY(A1:A10, "Select A where A like '%th'")
The following screenshot shows how to use this formula:
Method 3: Return Cells That Contain Certain Characters
We can use the following formula to return every cell in column A that contains ‘ou’ somewhere within the string:
=QUERY(A1:A10, "Select A where A like '%ou%'")
The following screenshot shows how to use this formula:
Additional Resources
The following tutorials explain how to perform other common queries in Google Sheets:
Google Sheets Query: Select Rows that Contain String
Google Sheets Query: How to Use Multiple Criteria in Query
Google Sheets Query: How to Use Cell Reference in Formula
Google Sheets Query: How to Select Multiple Columns