How to Use the Proper Function in Google Sheets Query

The Proper Function in Google Sheets

Before we get into the workaround, let’s briefly understand what the Proper function does. The Proper function in Google Sheets changes the case of the first letter in each word to capitalize it, while converting the rest of the letters to lowercase.

Now let’s see how we can use the Proper function in Google Sheets Query with a clever trick.

Using the Upper or Lower Functions in Google Sheets Query

Let’s say you have a dataset with names that are not grammatically correct, and some names appear more than once in non-identical letter cases. This can affect grouping and totaling in Google Sheets Query.

To tackle this issue, you can use the lower() or upper() scalar functions in the Select clause of the Query. Here’s an example:

=query(A1:B8,"Select upper(A), sum(B) group by upper(A)",1)

By using the upper() function, you can achieve accurate grouping and totaling. However, if you want the grouped output in the proper case, you’ll need to use a slightly more advanced formula:

=ArrayFormula(query({proper(A1:A8),B1:B8},"Select Col1, sum(Col2) group by Col1",1))

In this formula, I’ve created a virtual range that contains proper names in column A and corresponding amounts in column B. To achieve this, I’ve used the Proper function, along with Curly Brackets and ArrayFormula.

Please note that instead of Curly Brackets, you can also use HSTACK. In that case, simply replace {proper(A1:A8),B1:B8} with hstack(proper(A1:A8),B1:B8).

Adding Proper Case to Filtered Queries

There’s one more scenario to cover. If you’re using a Query solely for filtering a table or if your Query’s output contains only text strings, you can wrap the Query with the Proper() function.

For example, let’s say column A contains employee names in mixed-case letters, and column B contains the letters ‘A’ for “absent” and ‘P’ for “present”. If you want to filter all employees who are present and display their names in proper case, you can use the following formula:

=ArrayFormula(proper(query(A1:B12,"Select A where B='P'",1)))

Conclusion

Using the Proper function in Google Sheets Query may not be straightforward, but with these clever techniques, you can achieve the desired results. Remember, by using the upper() or lower() functions in the Select clause, you can accurately group and total your data. And if you want the output in the proper case, you can use the ArrayFormula and wrap the Query with the Proper() function.

If you want to learn more about Google Sheets and its advanced features, visit Crawlan.com for tutorials and tips.

Happy querying!