by Natalia Sharashova, updated on
How can we change the case of the first letter from lower to upper in Excel cells? Do we have to type each character manually into every cell? Not anymore! Today I will share three methods of capitalizing first letters in your table.
I believe when it comes to text in Excel, one of the most commonly required tasks is to capitalize first letters in the cells. Whenever you have lists of names, products, tasks, or anything else, you will for sure have some of them (if not all) written only in small or capital letters.
In one of our previous articles we discussed how PROPER function can save the day. But since it capitalizes every word in a cell and lowers other letters, sometimes it can't be a cure-all.
Let's see what other options we have on the example of a shortlist of villains I like the most.
Excel has lots of useful functions suitable for capitalizing first letter in the cells. However, you can't have both, your data and formula that references it, in one cell. Thus, you need to create a helper column somewhere in your worksheet in order to place the formulas there. When it's done, and calculations are made, you'll be able to replace formulas with their values. Shall we begin?
To make only the very first letter capital in Excel cell and lower the rest at the same time, start with inserting an additional column for the results. In my example it is column B. Right-click the column name (B) and choose Insert from the context menu. The column is inserted between A and C columns, and you can change its header name if there is one:
Put the cursor into new B2 cell and input the following formula there:
=REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))
Tip. It is most likely that the rest of the rows will be populated with the adjusted formula automatically. Otherwise, you can quickly copy the formula down the column by drag-n-dropping or double-clicking that small square in the lower-right corner of the cell with a formula.
Let me explain what the formula above means:
Thus, you get properly looking cells written as sentences.
In order to capitalize the first letter of the cell and leave the other characters as they are, we will use the same formula as above with a little modification.
But first, again, make sure to create another column to use the formula. Then, enter the following into B2:
=REPLACE(C2,1,1,UPPER(LEFT(C2,1)))
See, we deleted that "LOWER" part from the beginning of the formula. This little change won't lower all the letters in a cell but will still capitalize the first one:
Tip. Don't forget to copy the formula down if Excel hasn't done it automatically.
If you decide that you need a quicker and faster way of making first letters in Excel cells capital, you will choose wisely!
Our Change Case from Text Toolkit will look over those pretty little letters of yours. It is available in the collection of 70+ toos for Excel - Ultimate Suite:
The add-in pane will appear on the left side of your Excel window.
Tip. You can select the range before running the tool. It will show the selected range in the corresponding field automatically.
Note. If you want to save a copy of your data just in case, tick off the Back up the worksheet option before making any changes.
Note. When every word in a cell (except the first one) starts with a capital letter, the add-in will not only capitalize the first character, but also lower the rest.
As you can see, capitalizing letters in Excel is not rocket science. Now you can do it in a couple of mouse-clicks and enjoy the results. Feel free to leave comments and ask questions down below :)
Table of contents