Capitalize all letters in excel Using VBA and functions

By |2019-02-16T23:47:56+00:00April 21st, 2018|

If you haven’t yet, through your journey with excel, you might soon chance a situation where you 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 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 (i.e. Proper Case).

You know in Microsoft Word, you can simply do all these stuff with just a few clicks.

However, things are different in Excel.

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

The UPPER function, as the name implies converts text from lowercase to upper case letters.

The LOWER function, on the other hand, converts text to lower-case.

And the PROPER function converts text to initial-case.

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

See screenshot:

Change Case in Excel

You can use functions as well as VBA to capitalize text in Excel.

I personally prefer using Excel VBA because it makes it possible and easy to change the case of text in an entire worksheet or a selected range of cells.

Without the use of some macro, you cannot capitalize 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.

Thus, if you have more than 10 rows in your worksheet, there’s more work for you, since 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

Change Case in Excel

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 fire up the Visual Basics Editor.

After pressing these keys you’ll see a window like the one below:

Change Case in Excel
  • Go to Insert→Module
Change Case in Excel

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

Change Case in Excel
  1. Now type or copy and paste the VBA code.

If you are typing, please type it as it is.

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

See screenshot:

Change Case in Excel
  1. 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 sell 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:

Change Case in Excel

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:

Change Case in Excel

Also read: Over 10 actionable VLOOKUP examples with explanations.

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

The first one can change text from lower-case to upper-case, and the second one will change 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
Change Case in Excel
  • 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.

Change Case in Excel

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.

Let’s take a look at the worksheet below:

how to capitalize all letters in excel

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

Assuming that you want to capitalize every name in the name column.

The following steps will show you 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:

how to capitalize all letters in excel

  • Rename your column with the previous column name.

Thus, 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 inserting 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)

how to capitalize all letters in excel

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

how to capitalize all letters in excel

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.

how to capitalize all letters in excel

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.

how to capitalize in excel

Now that you have your capitalized names, you will want 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 the Ctrl+V shortcut to paste the data because it won’t paste as values. Since we want to get rid of the functions leaving the data, we can’t use the Ctrl+V shot cut.
  • Instead, 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.

how to capitalize in excel

  • Now past as values using the Past Values option.

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 analyze 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 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.

how to capitalize in excel

The above technique can be used to change the case from upper-case to lower case as well.

You can also change from other cases to proper-case where the beginning letter for each word is capitalized.

To change from lower-case to upper-case, use the =UPPER() function.

To change from upper-case to lower-case, use the =LOWER() function.

To capitalize only initial letters of each word, use the =PROPER() function.

Thanks very much for following this guide to the end.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I Know You Better!
Subscribe To My Newsletter
Be the first to get latest Excel updates and
exclusive content straight to your email inbox.
Yes, I want to receive updates
No Thanks! I Don't Want to Learn Excel.
close-link

Not Sure What to Learn in Excel?

Sign Up For My Actionable Excel Techniques You Can Use Right Away!
Send me Updates
You'll Get One Email Every Week!
close-link