Sometimes you’ll have an extensive set of data that just isn’t appealing to look at.
This happens for example when you receive data from a survey form that students filled out. Or when you just type in data without worrying about capitalization.
Thankfully there is an easy way how to capitalize the first letter in Google Sheets.
Working with Google Sheets for more than 7 years has taught me how to make use of functions such as the PROPER function. I wish I knew this one when I just started out using Sheets.
In this tutorial, I am going to discuss how to capitalize the first letter in Google Sheets in great detail.
How to Capitalize the First Letter in Google Sheets
The easiest way to capitalize the first letter in Google Sheets is by using the PROPER function. Follow the steps below:
- Define the text or the cell containing the text for capitalization
- Select a cell where you’d like the capitalized text to be
- Type in the formula “=PROPER(cell_reference)” in that cell
How to Capitalize The First Letter in Google Sheets Video Tutorial
The PROPER Function in Google Sheets
We use the PROPER function to capitalize the first letter in Google Sheets. Here’s the syntax:
=PROPER(cell_reference)
Take note that the Proper function does resolve the need to capitalize the first letter in Google Sheets but while it does capitalize the first letter of every word in a cell, it also changes every other letter to lowercase.
Let’s move on.
How to Use the PROPER function to capitalize the first letter in Google Sheets
The PROPER function is very simple to use in that you just need to reference the cell where you have the string that you wish to capitalize the first letter in Google Sheets.
It is best used in Proper Nouns to change the first letter of the words to upper-case, as is the right way to write them.
It works in a string in that each word, separated by a space or punctuation marks, has its first letter changed to an upper-case one as long as it is a lower-case letter.
If it’s already capitalized, nothing will happen. Numerical characters will be skipped.
Additionally, the other letters in every word other than the first character will be changed to lowercase.
In our example below, you can see the string “king richard III” on cell B9. Using the PROPER function, we get =PROPER(B9) and the result is “King Richard Iii”.
In the given string, we got these changes:
king → King
Richard → Richard
III → Iii
For the first 2 words, the first letter got capitalized while the last word “III” got its first character retained as an upper-case letter, but got the rest changed to lower-case.
Capitalize the First Letter in Google Sheets — ARRAYFORMULA
Spreadsheets are often used to facilitate big data with hundreds to thousands of entries. For example, a survey where customers put in their info like addresses.
It is difficult to impose rules regarding data input to customers so they often put their information in different ways like addresses being inputted without proper capitalization.
This is where the PROPER function works best. That said, survey forms can exceed thousands of entries, and to avoid having to set the PROPER function formula for new cells each time, you can instead use ARRAYFORMULA.
As described in the Google Help Center – “ARRAYFORMULA enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.”
In the example below, the data from column A (cell reference is A2:A indicating that the whole column A is selected except A1 which contains the column header “Street Adress”) is used for the formula on column B.
=ARRAYFORMULA(PROPER(A2:A))
This formula only needs to be inputted in a single cell and it will continuously display all the results in the destination column (B2:B) as long as there is information available in the source column (A2:A).
With this setup, even if an extra 100 rows are added, the ARRAYFORMULA applied to ranges will automatically capitalize the first letter in Google Sheets.
How to Capitalize the Entire Word in Google Sheets
If for some reason, you need to capitalize the entire word, you may use the UPPER function instead of the PROPER function.
Similar to the PROPER function, UPPER’s syntax is the following:
=UPPER(cell_reference)
As you can see below, the whole string “king richard III” is converted to upper-case and not just the first letters of each word.
Using the PROPER function is really helpful to capitalize the first letter in Google Sheets. This is important as data visualization is really important and its presentation is key – capitalization included.
Other than this, you may also want to consider making a Dot Plot in Google Sheets.
A Dot Plot, also called a Dot Chart, is a simple tool to visualize data points/values. The data is expressed in form of dots on the graph with either the x-axis or y-axis or both.
Frequently Asked Questions on How to Capitalize the First Letter in Google Sheets
I am trying to make a formula for paragraphs. How can I capitalize the first letter in Google Sheets where only the first word in a string is affected?
You may capitalize the first letter in Google Sheets for paragraphs by using the CONCATENATE, LEFT, RIGHT, and LEN functions in addition to the PROPER function. Use this formula: “=CONCATENATE(PROPER(LEFT(A2,1)),RIGHT(A2,LEN(A2)-1))“
Can I capitalize the first letter in Google Sheets for each paragraph while the rest of the paragraph remains unchanged?
You can capitalize the first letter only of each paragraph by using this formula: “=CONCATENATE(PROPER(LEFT(E2,1)), RIGHT(E2,LEN(E2)-1), CHAR(10), CHAR(10), PROPER(LEFT(F2,1)), RIGHT(F2,LEN(F2)-1))“
If you need more information about this, don’t hesitate to comment below.
Conclusion on How to Capitalize the First Letter in Google Sheets
To capitalize the first letter in Google Sheets, define the text or the cell containing the text for capitalization. Then select a cell where you’d like the capitalized text to be and type in the formula, “=PROPER(cell_reference)” in that cell.
