TG Tip: Using a Spreadsheet to Correct Capital Usage in Titles

Posted on May 5, 2014 1:00 PM by Rob Williams

A couple of weeks ago, I ripped an audio CD to my PC. To save the hassle of typing out the title to each track, I took advantage of a free online database built with the help of user submissions. Normally, I’m pleased enough with the result that I don’t have to change anything, but not this time. Whoever submitted this particular track list wrote the titles out as if they were sentences, with only the first letter capitalized.

In a situation like this, sucking it up and manually writing out the tracklist is actually less tedious than fixing the errors. Even easier? Using a spreadsheet application like Microsoft Excel or LibreOffice Calc to correct the issue for you, or at least mostly so.

The command is called PROPER, and its formula is simple: =PROPER(A1)

When the PROPER function is executed, it will automatically capitalize the first letter in each word for the cell that it’s applied to. Here’s an example of the function in action with Excel:

Microsoft Excel - Use of PROPER

And here it is in LibreOffice Calc:

LibreOffice Calc - Use of PROPER

To apply the PROPER function to multiple cells, it must first be used in a single one; afterwards, click on the cell and copy it using Ctrl + C. The resulting text won’t be copied here, but instead the cell’s underlying PROPER command. Once done, highlight all of the cells you want to apply PROPER to, and paste the function with Ctrl + V.

As time-saving as the PROPER function is, the results are not always flawless. Words like “a”, “the” and “of” wouldn’t be capitalized in most titles (unless they’re the starting or ending word), so you might still need to fine-tune a bit afterwards. I’ve also noticed that PROPER considers any letter after a punctuation mark to be a new word, so you may see occasional results like Door’S.

Despite those little issues, the PROPER command has the potential to save you a lot of effort and mind-numbing tedium.

Want to capitalize every letter in a block of text, or make all letters minuscule? The functions UPPER (eg: =UPPER(A1)) and LOWER (eg: =LOWER(A1)) can help with that.

Recent Tech News
Recent Site Content