Using the PROPER function to capitalize the first letter of each word in Google Sheets

Note: This post was originally published in our community forum.

Ever found yourself needing to clean up a list of names, titles, or any other text in Google Sheets, ensuring each word starts with a capital letter? Whether you’re preparing a presentation, organizing data, or just trying to make your sheets look neater, I’ve got a neat trick for you.

Google Sheets offers a powerful function called PROPER that can do exactly this. However, when you have a long list, editing each cell individually is not practical. That’s where the ARRAYFORMULA function comes in, allowing us to apply the PROPER function to a range of cells all at once!

Let’s create an example scenario where this formula can be applied in Google Sheets.

Imagine you have a list of product names in your sheet that are not consistently formatted. Some names are all lowercase, some are all uppercase, and others are a mix of both. You want to standardize the formatting so that each word in the product names starts with a capital letter, and the rest of the letters are lowercase. Here’s how you can do it:

Original Data

Column A

apple iPhone

SAMSUNG galaxy S21

google Pixel

Step 1: Select the cell where you want the output to start. In this case, let’s say you want to place the formatted names in Column B, starting at B2.

Step 2: In cell B2, enter the formula to capitalize the first letter of each word across your range of product names in Column A (assuming your list starts at A2 and ends at A4):

=ArrayFormula(PROPER(A2:A4))

Step 3: Press Enter. The formula will automatically apply the `PROPER` function to each cell in the range A2:A4, and the results will be displayed in the corresponding cells in Column B.

Result

Explanation:

• The formula =ArrayFormula(PROPER(A2:A4)) takes each value in the range A2:A4, applies the PROPER function to capitalize the first letter of each word, and outputs the results into Column B, maintaining the row alignment.

• This process standardizes the formatting of each product name, making your data look cleaner and more professional.

Note: This example assumes your original data is in Column A and that you want the formatted output in Column B. Adjust the cell references in the formula as needed based on where your data is located and where you want the results to appear.