If you haven’t yet, you’ll soon chance in a situation through your journey with excel, where you’ll want to manipulate text by changing the case, I mean capitalization. In this tutorials, 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.
You know in Microsoft Word, you can simply do all these 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 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 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.
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 the above code.
- After launching your Excel, press Alt+F11 to launch the Visual Basics Editor. After pressing these keys you’ll see a window like the one below:
- Now, look through the tabs until you set your eyes on the Insert Tab. Click on it and then click on Module. Now a white text editor should appear.
- Start typing the VBA code above. Please type it as it is. But if you want your case to be in lowercase, kindly change the UCase to LCase.
- After typing the code, switch back to the worksheet and select the area or range of cells you wish to capitalize. After the selection, switch back to the VBA code and press the F5 key. Every selected sell should be capitalized successfully.
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:
- 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, 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)
After inserting the function press the enter key to complete inserting the function.
Now, look what happened: After I press 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 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 tutorials. If you have any more questions, please don’t hesitate to ask.