Suppose you have created an Excel spreadsheet but unfortunately used lowercase for all the cells containing texts you needed to make uppercase. In this article, I will show you the method to change the texts from lowercase to uppercase in an Excel Spreadsheet with formula.
How to Change Text from Lowercase to Uppercase with Formula in Excel: 3 Common Methods
In this section, you’ll find 3 easy methods for converting text from lowercase to uppercase in an Excel workbook using Excel built-in formulas. Let’s check them now!
1. Using UPPER Function to Change to Uppercase
Suppose, we have a dataset of different customers and the price of their purchased products. Here, all the texts describing the customers’ names are in lowercase letters and we want them uppercase.
Excel provides the UPPER function that can help us changing the case to uppercase. Let’s start the procedure.
- At first, insert a new column left to the column that contains the text you want to convert.
- Add the following formula to the cell of the new column in which you want to get the result.
here,
- D5= the cell you want to convert
- Press ENTER and you will get the output.
- Use Autofill to drag the Formula to the rest of the cells you want to get similar results.
- Copy the value of the new column and Paste it to the previous column where the main data exists. Make sure you have used the Paste Values option.
- After pasting the values, delete the previous column so that it doesn’t show the formula.
That’s how we can change the lowercase of a text to uppercase just by using the formula with the UPPER function.
Read More: How to Change Case in Excel Sheet
2. Converting Texts to Uppercase with PROPER Function
Previously you have seen the way of converting all the characters into upper case. Let’s say, you only want to capitalize only the first letter of each word. By using this method you will be able to perform this task. In this method, you will see the use of the PROPER function.
So, let’s start the procedure.
- First, add a column left to the column containing the main data and add the following formula to the cell where you want to get the result:
here,
- D5= the cell you want to convert
- Press ENTER and you will get the output.
- Use Autofill to drag the Formula to the rest of the cells you want to get similar results.
- Copy the value of the new column and Paste it to the previous column where the main data exists.
- After pasting the values, delete the previous column so that it doesn’t show the formula.
Thus we can capitalize only the first letter of the texts when we need it by just using this formula with the PROPER function.
3. Using VBA to Change Lowercase to Uppercase
Let’s say, for the previous dataset, you want to convert text from lowercase to uppercase. By using VBA macros, you will also be able to perform this task.
So, let’s start the procedure.
- First, from the worksheet press ALT+F11 together to open the Visual Basic Editor window.
- Right-click on your workbook from the “Project-VBAProject” pane, you will find that at the top left corner of the editor window, and from there select as follows Insert > Module.
- Copy the VBA code and paste it to the code window of the VBA editor. For converting lowercase to uppercase, you can use the following:
Option Explicit
Sub ChUppercase()
Dim Cells As Object
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cells In Selection
If Not Cells.HasFormula Then
Cells.Value = UCase(Cells.Value)
End If
Next Cells
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
- The VBA UCASE function converts the values to all uppercase from the selection.
- The Application.ScreenUpdating = False Application.Calculation = xlCalculationManual lines to make your macro code execute faster. The names are self-descriptive, turn off screen refresh.
- To the very end of the code, just before End Sub write the Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic lines, these will allow to recalculate formulas from the workbook before running the code. - Save the code and press ALT+Q to switch back to your active worksheet.
- Select the cells you want to convert & Press ALT+F8 to open the Macro dialogue box. Select ChUppercase from the Macro name and click Run.
- You will get the desired output.
That’s how we can convert lowercase texts to uppercase by using VBA macro.
Read More: Excel VBA to Capitalize First Letter of Each Word
Download Practice Workbook
Conclusion
In this article, we have learned how to change texts in Excel from lowercase to uppercase using built-in Excel formulas. I hope from now on you can quickly change the text from lowercase to uppercase if you need them. If you have any queries regarding this article, please don’t forget to leave a comment below. Have a great day!
