How to Apply Bulk Change Case in Google Sheets Using Query Function

4

With bulk change Case in Google Sheets, what I meant to say is; change the case of complete texts in a table, data range or multiple columns at a time. For this, we are using Google Sheets QUERY formula. Didn’t get?

I am talking about replacing text to Upper or Lower Case in Google Sheets but a bulk change. If you want the simple approach using Lower, UPPER, and Proper functions, go to our below trending guide.

Similar: Change Text to Upper, Lower or Sentence Case

Let’s see how to apply the bulk change case using Google Sheets Query Formula.

Bulk Change Case in Google Sheets Using Query

Below is the sample data. Here we are going to capitalize first two columns at a time using Google Sheets Query formatting Clause.

sample data for bulk change case

Here is the Query formula for the above range to change the text in Column A and Column B at a time to UPPER Case.

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

Here A1:D17 is the above data range. Here I’m capitalizing Column A and Column B which contain text values while keeping column C and D as it is. See the image below.

change text to upper or lower case of multiple columns at a time

The same formula you can use.

How to replace the existing table with the capitalized one?

  1. Go to the cell which contains the formula. Here it’s Cell F1.
  2. Use keyboard shortcut Ctrl + C to copy it.
  3. Go to Cell A1. Use Alt+E+S+V or Right Click on the cell to access Paste Special > Values.

Now you can delete the cell that containing the Query formula.

Limitation

Query formula only supports two of the Case functions. They are UPPER and LOWER. At this time the PROPER case is not added to QUERY.

Conclusion

The Query is a very advanced function in Google Sheets. You should spend the time to learn the Query.

We have plenty of basic to advanced Query related tutorials here on Info Inspired. You can either search on our site or go to our Google Sheets function Guide to start learning Query.

Here are a few quick picks.

  1. How to Format Date, Time, and Number in Google Sheets Query.
  2. Google Sheets Query to Filter Numbers Only From Mixed Data Column.
  3. Google Sheets Query: How to Convert Month in Number to Month Name in Text.
  4. Learn Query Function with Examples in Google Sheets.
Prashanth KV
Introducing 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.

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