Eshrak Kader
While working with Microsoft Excel, we may need to capitalize the first letter of words such as employee names, texts, etc. Granted this, the following article demonstrates 6 easy ways how to change first letter to uppercase in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
6 Methods to Change First Letter to Uppercase in ExcelÂ
It is common for Excel users to change the case of texts in their spreadsheets. So, without further delay, let’s explore the process step-by-step.
Assuming the dataset given below in the B4:D14 cells. Firstly, we have the Store number, next, the Location of the store is shown, and finally, the employee Names are given.
1. Change First Letter to Uppercase with PROPER Function
The PROPER function converts the text string to the proper case. Simply put, it capitalizes the first letter of a word while keeping the other letters in lowercase. So, let’s see it in action.
Steps:
- Firstly, insert a new column with a suitable column heading.
- Next, type in the PROPER function and select the D5 cell.
=PROPER(D5)
Here, the D5 cell refers to james.
- Then, drag the Fill Handle tool to copy the formula in the cells below.
Finally, the results appear as shown in the picture below.
Read More: Excel Change to Proper Case without Formula (6 Easy Ways)
2. Using Flash Fill Option to Change First Letter to Uppercase
If using formulas doesn’t suit you then you’ve come to the right place. Since the Flash Fill option will quickly and accurately enter the data for you. Based on the initial item, it auto-completes the rest of the data. Now, let’s follow the quick steps below.
Steps:
- At the very beginning, type in the first entry from the D5 cell but capitalize the first letter. In our example, james as James.
- Secondly, confirm your entry by pressing CTRL + ENTER.
- Lastly, press CTRL + E on your keyboard to auto-complete the table.
If you want, you may explore more uses of Flash Fill in Excel.
Read More: How to Change Case for Entire Column in Excel (7 Amazing Ways)
3. Using Keyboard Shortcut to Change First Letter to Uppercase
In case you’re wondering about keyboard shortcuts to change case then I have some good news for you. Allow me to demonstrate the procedure below.
Steps:
- Initially, create a new column and select the first cell
- Then, press SHIFT + F3 on your keyboard.
- In turn, a dialog box appears where you search for the PROPER function as shown in the image below.
- Secondly, choose the cell reference in the Text box and click OK.
Now, this capitalizes the first letter, in this case, james as James.
- Finally, use the Fill Handle tool to complete the table.
Read More: How to Change Lowercase to Uppercase in Excel (6 Methods)
4. Utilizing LEFT and RIGHT Functions to Change First Letter to Uppercase
If you’re one of those people who enjoy using Excel formulas then our next 2 methods have you covered. In fact, we’ll apply a combination of LEFT, RIGHT, and UPPER functions to change the first letter to uppercase. Just follow along.
Now, let’s consider the dataset in the B4:B14 cells which contain a list of Idioms with all the words in lowercase.
Steps:
- To begin with, make a new column and insert the formula given below.
=UPPER(LEFT(B5,1))&(RIGHT(B5,LEN(B5)-1))
In the above expression, the B5 cell refers to the Idioms (Given Case).
Formula Breakdown:
- In the above formula, the B5 (text argument) and 1 (num_chars argument) returns the first alphabet of the word from the left.
- Next, the UPPER function converts this text to uppercase.
- Then, the LEN function gives the number of characters in the B5 cell.
- Secondly, the RIGHT function extracts text from the right side of the B5 cell (text argument) up to the position LEN(B5)-1) (num_chars argument).
- Now, this includes all the texts except the first letter.
- Finally, both the texts are combined using the Ampersand (&) operator.
Eventually, use copy and paste the formula to populate the remaining cells.
Read More: How to Change Sentence Case in Excel (6 Easy Methods)
5. Applying Formula Combination of REPLACE, UPPER, and LOWER Functions
What if the first letter of all the words is in uppercase whereas you need to capitalize only the first word? Don’t worry, our next method will show you the process bit-by-bit. Additionally, we will employ a combination of the REPLACE, LOWER, UPPER and RIGHT functions to get the results.
Steps:
- To start, insert the following formula in the new column.
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))
Here, the B5 cell points to the Idioms (Given Case).
Formula Breakdown
- Firstly, the REPLACE function takes 4 arguments and replaces part of the string with another string.
- In this formula, LOWER(B5) is the old_text argument while 1 is the start_num argument.
- Following, the next 1 is the num_chars argument, and lastly UPPER(LEFT(B5,1)) is the new_text argument.
- Here, all the text is converted to lower case with the LOWER function then the LEFT function extracts only the first letter of the first word.
- In turn, the UPPER function capitalizes only the first letter.
Consequently, applying the same formula to the other cells return the outcome shown below.
6. Using VBA Code to Change First Letter to Uppercase
Although using Excel formulas is convenient, however it can be difficult to interpret. Furthermore, if you have to frequently capitalize texts, then you may consider the VBA code below.
Step 01: Open the Visual Basic Editor
- Firstly, go to the Developer > Visual Basic.
Step 02: Insert the VBA Code
- Secondly, insert a Module where you’ll paste the VBA code.
For your ease of reference, you can copy and paste the code from here.
Sub Uppercase()
Dim i As Range
Set i = Selection
For Each cell In i
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
Next cell
End Sub
This VBA Code has been taken from TrumpExcel.com
Code Breakdown:
Now, I will explain the VBA code for changing text to uppercase. In this case, the code is divided into 3 parts.
- 1- Firstly, the sub-routine is given a name, and the variables are defined.
- 2- Secondly, assign the Selection property to the variable i. This allows us to choose a range of cells where we can perform operations.
- 3- Thirdly, we use a For loop to run the UCase function which capitalizes the first letter extracted by the LEFT function and joins it with the text returned by the RIGHT function.
Step 03: Running the VBA Macro
- Thirdly, close the Visual Basic Editor.
- Then, make a copy of the dataset and select the column.
- Next, click on the Macros option.
- Moreover, a dialog box appears with the name of the sub-routine where you have to choose Run.
Consequently, the first letter of the text will be changed to uppercase.
Read More: How to Make First Letter of Sentence Capital in Excel (4 Suitable Methods)
Conclusion
To conclude, I hope this article helped you understand how to change the first letter to uppercase in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
