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.
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.
The same formula you can use.
How to replace the existing table with the capitalized one?
- Go to the cell which contains the formula. Here it’s Cell F1.
- Use keyboard shortcut Ctrl + C to copy it.
- 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.
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.
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,