I work with a lot of Excel users. They send me a lot of questions. “How do I capitalize letters in Excel?” “How do I change the formula cell to add a 2 in front of the cell and 1 after it?” “How do I change the cell in column A to add a 2 after it?” “How do I change the cell in column A to add a 3 after it?” “How do I change the cell in column A to add a 5 after it?” “How do I change the cell in column A to add a 6 after it?” “How do I change the cell in column A to add a 7 after it?” “How do I change the cell in column A to add a 8 after it?”
Microsoft Excel is a powerful tool to use to analyze and organize data. If you want to capitalize the first letter of every word in a cell, you can do so easily using VBA.
Today, I am going to share Excel’s most underrated function. It’s an incredibly useful function that, for the most part, you will not use on a daily basis. But, if you need to capitalize all letters in a cell, this function can save you time and make your Excel life easier.
If you haven’t already, you’ll soon find yourself in a position where you need to modify text by changing the case, or capitalization, of the text.
In this article, I’ll show you how to alter the text cases in Excel.
You may wish to modify the text from lower case to upper case, for example. Alternatively, you may change the case from upper to lower case. It’s also possible to alter the case of each word so that just the initial letters are capitalized.
You know how easy it is to accomplish all of this in Microsoft Word with just a few clicks? However, things are different with Excel.
The UPPER function, the LOWER function, and the PROPER function are all built-in Excel functions for changing cases.
The UPPER function changes lowercase to uppercase characters in text.
On the other side, the LOWER function transforms text to lowercase.
The PROPER function, on the other hand, returns text to its original case.
Except for the initial letter of each word, the PROPER function converts all letters to lowercase.
In Excel, you can capitalize words using functions, or you may utilize VBA code to do so.
However, I prefer Excel VBA since it allows me to capitalize a whole page or a specific range of cells quickly and easily.
You can’t take use of a certain range of cells at once without using VBA code. Instead, you’ll have to use the methods to modify them row by row or column by column.
So if your worksheet has more than 10 rows, you’ll have extra work to do. Because you’ll have to update each column one at a time for all of the columns.
If the text you wish to capitalize is just in one or two columns, however, I believe that utilizing functions will be far more easy and quicker.
First and foremost, let’s discuss how to capitalize in Excel using VBA. Following that, we’ll learn how to accomplish it using Functions.
How to capitalize in Excel using Visual Basic for Applications (VBA)
As I previously said, you may change the case of a range of cells or the whole worksheet using VBA code. In Microsoft Word, this is as easy as a few clicks, but in Excel, things are a little more complicated. Without spending any more time, here is the code for converting your text to capital or tiny characters.
ChangeCase is a subclass of ChangeCase ()
SelectedCells Is Range Dim SelectedCells Is Range Dim SelectedCells
Each of the SelectedCells in the Selection. Cells
If SelectedCells is true. If HasFormula is False,
UCase = SelectedCells.Value (SelectedCells.Value)
If you want to stop, just say so.
SelectedCells is the next step.
Your chosen cells’ case will be changed to UPPERCASE using the code above. If you’re not familiar with Excel VBA, here’s how to utilize this code.
- Excel should now be open.
- To open the Visual Basics Editor, press Alt+F11. You’ll see a window similar to the one below after hitting these keys:
3. Select Insert Module from the drop-down menu.
This operation will open a text editor in which you may type your VBA code.
- The VBA code should now be typed or copied and pasted.
Please enter it exactly as you see it.
Please replace the UCase to LCase if you want your case to be in lowercase.
Take a look at this example:
- Now you may execute the code.
Return to the worksheet after entering the code and pick the region or range of cells you want to capitalize.
Switch back to the VBA code and hit the F5 key after you’re through with the selection.
Every chosen cell should be successfully capitalized.
How to attach a button to a macro code
You may attach a button control to your macro.
You may change cases in Excel with a single button click this way.
Take a look at this example:
The methods for assigning your macro code to a button control are outlined below.
- To make two distinct versions of the code, insert two modules.
The macro code for the upper-case and lower-case button controls is different in the preceding example.
Take a look at this example:
The macro code in these two modules is almost identical. They are, nevertheless, distinct.
The first can alter the text’s case from lower to upper, while the second can change the case from upper to lower.
You’ll also note that the function names in both modules have been updated.
If the functionality of both modules are the same, an error may occur.
Let’s move on to the button controls.
- Return to the worksheet where the buttons will be added.
- Insert a button control by going to DeveloperControlsInsertButtonControl.
- Drag the button control to any location on the spreadsheet.
The Assign Macro dialogue displays as soon as you draw the button.
Click OK after selecting the code you want to give to your button.
Excel will now run the Macro you set to the button when you click it.
Changing case with the Upper function
You may learn how to capitalize using Excel functions by watching the video below.
Now that we’ve had success using VBA, let’s look at how to accomplish the same outcome using Excel’s built-in functions.
I copied the following information from a hospital database to use as an example.
Assume we want to uppercase all of the names in the name column. The steps below will teach us how:
- Next to the text column, add an empty column.
First and foremost, create an empty column to the right of the one you want to uppercase.
In my instance, I’ll add an empty column after column A, as illustrated in the illustration below:
- Replace the name of your column with the name of the preceding column.
As a result, I’m renaming my table column NAME.
The capitalized copy will be in this new column that we have established.
Don’t worry, I’ll teach you how to copy them back to the original column following the capitalization.
- Write the UPPER() function now.
In the cell of the newly formed column where you want your capitalization to start, type the UPPER function.
Because I want my capitalization to start in the second cell of the column, I’ll put the function there.
=UPPER =UPPER =UPPER=UPPER=UPPER=UPPER=UPPER=UPPER=UPPER (A2)
After you’ve finished entering the function, hit the Enter key to finish it.
Let’s take a look at what occurred now.
The function rapidly changed all of the text in cell A2 to uppercase after pressing the enter key.
Every word in the original cell is capitalized correctly in the new cell.
As a result, in cell B2, the text in cell A2 is now capitalized.
But there’s something crucial you should know about this.
Excel will automatically replicate the function in the remaining cells of the table if you use Excel tables to arrange your data.
However, if your data doesn’t have a table, you’ll have to manually duplicate the formula into the remaining cells.
Here’s how to do it quickly.
There’s no need to spend time here since there’s a way around it.
- Simply choose the cell containing the function you just typed. This is to ensure that the cell is functioning properly.
- Now double-click on the tiny black square in the chosen box’s bottom-right corner.
Have you seen what just happened?
Excel will apply the function to the remainder of the cells automatically.
As you can see, the whole column has now been converted to capital letters from tiny letters.
Now that this step is complete, you must delete column A, which you no longer need.
However, you can’t simply remove Column A since it’s referenced in Column B.
So, you’ll copy the whole contents of column B and paste them as values in the same column.
- Click on the column letter, which in my instance is B, to copy the whole column. The whole column is chosen, as you can see.
- To copy the data, use Ctrl + C on your keyboard. Please do not paste the data using the Ctrl+V shortcut since it will not paste as values. We can’t utilize the Ctrl+V shot cut to paste since we want to remove the functions while keeping the data.
- Go to the Home tab and click the Arrow underneath the paste option to paste as values. You’ll be able to view all of the previous choices. The paste values option may be found if you browse through the settings.
Your data will now appear as though nothing has occurred. But that isn’t the case since there was a lot going on in the background. If you look closely at the data, you’ll see that the function we used to uppercase the text has vanished entirely.
That’s precisely what we want, to get rid of the background functions and leave just the capitalized list.
You may now remove the column that contains lowercase text. Column A will be deleted in my instance.
Right-click on the column letter to remove it. Then, to eliminate the whole column, click delete.
We’ve reached the conclusion of this tutorial. Please do not hesitate to contact us if you have any further inquiries.
In this post, we will show a simple way to capitalize all the letters in a cell or range of cells in the Excel worksheet.. Read more about excel vba uppercase range and let us know what you think.
Frequently Asked Questions
What is the Excel formula to capitalize all letters?
=CAPITALIZE(LEFT(A2,LEN(A2)-1)) Q: How many calories are in a slice of pizza? 1 slice of pizza has about 170 calories.
Is there a quick way to capitalize all words in Excel?
Yes, you can use the keyboard shortcut Ctrl+Shift+F to capitalize all words in a cell.
This article broadly covered the following related topics:
- excel vba proper case
- excel vba uppercase range
- vba uppercase first letter
- vba uppercase
- ucase vba