Use QUERY to Bulk Convert Text Cases in Google Sheets

Published on

Using the QUERY function, you can convert text cases in specific columns of a table in Google Sheets to upper or lower case.

The QUERY function includes two scalar functions named UPPER and LOWER that are used for this purpose.

These scalar functions also play a key role in case-insensitive string matching within the QUERY function. When applying criteria to a column for case-insensitive filtering, you can specify the criteria in either lower case or upper case letters, and the values in the column will be converted accordingly for matching.

Let’s explore how to apply bulk text case conversion using the QUERY function in Google Sheets.

Bulk Text Case Change in Google Sheets Using QUERY Function

In the following sample data, I have data in columns A to D where columns A and B are text types. The data range is A1:D17, and the first row contains headers.

Sample Data:

Sample Data for Bulk Text Case Change Using QUERY

Here are a few examples of using the QUERY function to bulk convert text cases in the first two columns:

Example 1:

=QUERY(A1:D17, "SELECT UPPER(A), UPPER(B), C, D", 1)

This formula changes the text case of values in columns A and B to uppercase letters.

Example of QUERY Bulk Text Case Conversion

It follows the syntax QUERY(data, query, [headers]) where:

  • data: A1:D17
  • query: "SELECT UPPER(A), UPPER(B), C, D"
  • headers: 1 (the number of header rows in the table. If omitted, the function will detect the number of header rows automatically)

Example 2:

If you want to convert one column to uppercase and another column to lowercase, use this formula:

=QUERY(A1:D17, "SELECT LOWER(A), UPPER(B), C, D", 1)

Bulk Text Case Change Using QUERY While Retaining the Original Field Labels

An issue with applying bulk text changes using QUERY is the modification of headers (field labels).

When using scalar functions, you might encounter labels like upper(Group), upper(Task) if the headers are Group and Task, or upper(), upper() if there are no headers in the table.

To retain the original labels, you should use the LABEL clause:

=QUERY(A1:D17, "SELECT LOWER(A), UPPER(B), C, D LABEL LOWER(A) 'Group', UPPER(B) 'Task' ", 1)

If your original table has no header row, you can remove the labels added by these scalar functions:

=QUERY(A1:D17, "SELECT LOWER(A), UPPER(B), C, D LABEL LOWER(A) '', UPPER(B) '' ", 1)

Additional Note

We can’t apply the PROPER case in QUERY as there is no such scalar function available.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. Dear Prashanth

    I am not able to understand why you need to do it with a query. I think it can be done simply by an array function in one of the columns and then copy-paste-value only.

    Am I missing something?

    • I was just introducing how to use LOWER/UPPER functions in Query.

      The real purpose or real-life use of these two functions are not the bulk change case. It’s related to the case sensitivity of the Query function. I’ll write a tutorial on that and post the link below soon.

      In the meantime, you can check the below tutorial to get some idea about the case sensitivity of the Query and what I am going to write about.

      How to Use the Proper Function in Google Sheets Query.

  2. Great work, very useful.

    What is the regexp for taking out middle initials?

    For example in; John,P. Steward

    I want to take out every, initial. from every record in a column.

    Any examples?

    Thanks

    • Hi, Kenny,

      Assume all the names in the column follows the same format. If so for a range B2:B, you can use the below REGEX in C2.

      =ArrayFormula(IF(LEN(B2:B),REGEXEXTRACT(B2:B, "\,([A-Za-z]+)\."),))

      This formula would extract the initials separated by a command and a full stop as the separators.

      Best,

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.