How to convert strings to Proper case in Apps Script?
If you've worked with spreadsheets at a school or a company, you know that data can often be messy and not formatted correctly. One common issue that I've run into is that names are not capitalized correctly.
Google Sheets has a function called PROPER() that converts a string in your spreadsheet to Proper case, but unfortunately, there is no such function in Apps Script.
In this tutorial, you'll learn how to write a custom function to convert a string to Proper case.
Note
Capitalizing a string correctly is actually very complex since there are many rules and edge cases that must be considered to get the output to be correct 100% of the time. In fact, there are different sets of rules based on which convention you want to follow.
For example, if you want to convert a string to Title case (which is different from Proper case), there are several conventions that you could choose to follow:
etc.
Some of these conventions have fairly complex rules. For example, some rules depend on whether a word is being used as a noun or as an adverb. To implement these rules, we will also need our code to understand English grammar. This is non-trivial to do!
There doesn't seem to be an authoritative definition of how "Proper case" should be implemented, Therefore, please confirm that the code below will work for your use case before using it. If you find situations where it does not work, please do let me know via the contact form at the bottom of this page.
/**
* Convert a string to Proper case.
* @param {string} str The string value to be converted.
* @return The string value in Proper case.
* @customfunction
*/
function PROPER_CASE(str) {
if (typeof str != "string")
throw `Expected string but got a ${typeof str} value.`;
str = str.toLowerCase();
var arr = str.split(/.-:?—/ );
return arr.reduce(function(val, current) {
return val += (current.charAt(0).toUpperCase() + current.slice(1));
}, "");
}★ Code walkthrough
First, the code checks If the input is a string. If not, it throws an error to let the user know that it expects a string as input.
if (typeof str != "string")
throw `Expected string but got a ${typeof str} value.`;The code then uses three steps to convert the input string to Proper case:
Convert the input string to lowercase.
Split it into an array of values. The split points are determined by the presence of one of the following characters:
.,-,:,—,?,.Put these values back together while capitalizing the first letter of each value.
Step 1 — Convert the input string to lowercase
str = str.toLowerCase();Step 2 — Split it into an array of values
var arr = str.split(/.-:?—/ );Step 3 — Put these values back together while capitalizing the first letter of each value
return arr.reduce(function(val, current) {
return val += (current.charAt(0).toUpperCase() + current.slice(1));
}, "");Conclusion
In this tutorial, you learned how to write a custom function to convert a string to Proper case.
Thanks for reading!
Master Google Sheets Automation
I'd appreciate any feedback you can give me regarding this post.
Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!