Excel is a powerful tool with many features that can be utilized to make your work easier and more efficient. However, one of the most common frustrations with Excel is trying to capitalize all letters in a cell or range of cells. This article will teach you how to capitalize all letters in an Excel spreadsheet using functions or VBA.
The automatic uppercase in excel vba is a way to capitalize all letters in Excel with functions or VBA.
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.
End Sub
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.
The macro for uppercase in excel is a macro that will capitalize all letters in Excel.
Frequently Asked Questions
How do you capitalize all letters in Excel VBA?
To capitalize all letters in Excel VBA, you must first use the u key to capitalize the first letter of each word.
What is the Excel formula to capitalize all letters?
The Excel formula to capitalize all letters is =UPPER(A1)&Upper(A2)&UPPER(A3)&Upper(A4)
Is there a quick way to capitalize all words in Excel?
Yes, if you use the shortcut Ctrl+Shift+F3, this will capitalize all words in your document.
Related Tags
- what is the shortcut to change small letters to capital letters in excel?
- automatic uppercase in excel without formula
- vba uppercase first letter
- excel vba proper case
- how to change first letter to uppercase in excel
