How To Capitalize All Letters In Excel With Functions Or VBA

If you haven’t yet, you’ll soon come across a situation through your journey with excel, where you’ll want to manipulate text by changing the case, I mean capitalization.

In this tutorial, I want to focus on how to change the cases of text in Excel.

For instance, you want to change the text from lower case to upper case. Or from upper case to lower case. It is also possible to change the case so that only the first letters in each word will be capitalized.

You know in Microsoft Word, you can simply do all this stuff with just a few clicks. But in Excel, things are different.

There are three inbuilt Excel functions purposely for changing cases: The UPPER function, the LOWER function, and the PROPER function.

The UPPER function converts text from lowercase to upper case letters.

The LOWER function, on the other hand, converts text to lowercase.

And the PROPER function converts text to the initial case.

With the PROPER function, every letter will be converted to lowercase except for the first letter of each word.

You can use functions to capitalize in Excel and you can also use some VBA code to do the same.

But I personally prefer using Excel VBA because it makes it possible and easy to capitalize an entire worksheet or a selected range of cells.

Without the use of the VBA code, you cannot capitalize on a selected range of cells at once. But rather you’ll have to change them column by column or row by row using the functions.

So if you have more than 10 rows in your worksheet there’s more work for you. Because you’ll have to change each and every column one after the other for all the columns.

But if the text you want to capitalize is in only one or two columns, then I think using functions will be so much convenient and faster.

First of all, let’s talk about how to capitalize in Excel using VBA. After that, we’ll then learn how to do it using Functions.

How to capitalize in Excel with VBA

As I already mentioned, with the use of VBA code, you can select a range of cells or the entire worksheet and change it to whatever case you like. Doing this in Microsoft Word is very simple with a few clicks, but with Excel, things are different and tricky. Without wasting much time, below is the code that will convert your text to either capital or small letters.

Sub     ChangeCase()

            Dim SelectedCells As Range

            For Each SelectedCells In Selection.Cells

            If SelectedCells.HasFormula = False Then

            SelectedCells.Value = UCase(SelectedCells.Value)

            End if

            Next SelectedCells

End Sub

The code above will change the case of your selected cells to UPPERCASE. Now if you are new to Excel VBA, below is how to use this code.

• Open Microsoft Excel.
• Press Alt+F11 to launch the Visual Basics Editor. After pressing these keys you’ll see a window like the one below:

3. Go to Insert→Module

This action will display a text editor where you will enter the VBA code.

4. Now type or copy and paste the VBA code.

Please type it as it is.

However, if you want your case to be in lowercase, kindly change the UCase to LCase.

See screenshot:

5. Now run the code.

After typing the code, switch back to the worksheet and select the area or range of cells you wish to capitalize.

When you are done with the selection, switch back to the VBA code and press the F5 key.

Every selected cell should be capitalized successfully.

How to assign the macro code to a button

You can assign your macro to a button control.

This way, with just a button click, you can change cases in Excel.

See screenshot:

Below are the steps to assign your macro code to a button control.

• Insert two modules to create two different versions of the code.

In the above illustration, the upper-case and the lower-case button controls have different versions of the macro code.

See screenshot:

These two different modules contain almost the same macro code. However, they are different.

The first one can change the text from lower-case to upper-case, and the second one will change the text from upper case to lower-case.

You’ll also notice that I changed the function names in both modules.

It may result in an error if both modules have the same functions.

Now let’s insert the button controls.

• Switch back to the worksheet where you want to add the buttons.
• Go to Developer→Controls→Insert→Button Control

• Drag to draw the button control anywhere in the worksheet.

Immediately you draw the button, The Assign Macro dialogue appears.

Click to select the code you wish to assign to your button and click OK.

Now, when you click a button, Excel will execute the Macro you assigned to the button.

Using the Upper function to change case

You can use the video below to learn how to capitalize using excel functions.

Now that we found success with the VBA approach, Let’s now look at how to use Excel’s inbuilt Functions to achieve the same result.

I copied the above data from a hospital database so that we can use it as an example.

Let’s assume that we want to capitalize every name in the name column. The following steps will show us how:

• Add an empty column next to the text column

First thing first, create an empty column to the right just after the column you want to capitalize.

In my case, I’m going to create an empty column just after column A as shown in the picture below:

• Rename your column with the previous column name.

So I’m renaming my table column as NAME.

This new column we just created is the column where the capitalized copy will be.

And don’t worry, because, after the capitalization, I’ll show you how to copy them back to the original column.

• Now, Write the UPPER() function

insert the UPPER function in the cell of the newly created column where you want your capitalization to begin.

I am going to insert the function into the second cell of the column because that’s where I want my capitalization to begin.

My function will look like this: =UPPER(A2)

After inserting the function press the Enter key to complete inserting the function.

Now, see what happened.

After hitting the enter key, the function quickly converted all the text in cell A2 to uppercase.

Every text in the original cell is successfully capitalized in the new cell.

Thus, the text in cell A2 is now capitalized in cell B2.

But there’s something important you need to learn here.

If you use Excel tables to organize your data, Excel will automatically copy the function in the remaining cells of the table.

But if your data does not have a table, then you’ll have to copy the formula to the remaining cells yourself.

Below is an easy way to do that.

You don’t have to waste any time here because there’s a shortcut to do that.

• Just click the cell that contains the function you just entered. This is to make sure that the cell is active.
• Now double click on the small black square at the bottom-right corner of the selected box.

Did you just see what happened?

Excel automatically apply the function to the rest of the cells.

And as you can see, the entire column is now changed from small letters to capital letters.

Since this step is completed, you now have to get rid of column A which you don’t need anymore.

But you can’t just delete column A because you are referencing from it in Column B.

So, what you’ll do is copy the whole of column B and past it as values in the Same Column.

• To copy the entire column, click on the column letter, in my case it is B. You’ll see that the entire column is selected.
• Now press Ctrl + C on your keyboard to copy the data. Please don’t use Ctrl+V shortcut to paste the data because it won’t paste as values. Because we want to get rid of the functions leaving the data, we can’t use the Ctrl+V shot cut to paste.
• To past as values, go to the Home tab, and click on the Arrow below the paste button. You’ll see all the available past options. If you look through the options you’ll see the paste values option.

• Click on it to paste.

Now your data will look as if nothing happened. But that’s not the case, because a lot of things happened in the background. If you look at the data very well, you’ll notice that the function we use to capitalize the text has completely disappeared.

That’s what exactly we want anyway, to get rid of the functions in the background leaving only the capitalized list.

Now you are free to delete the column that has the lowercase text. In my case, I am going to delete column A.

To delete a column, right-click on the column letter. Then click delete to remove the entire column.

This brings us to the end of this tutorial. If you have any more questions, please don’t hesitate to ask.