Capitalize first letter in Excel to bring a uniform, professional look to your data, especially when dealing with names, titles, or any textual content. This simple yet impactful formatting can significantly enhance the readability and aesthetic appeal of your spreadsheets. Whether you’re compiling a list of customer names, categorizing products, or preparing presentation materials, this guide will navigate you through the straightforward steps to capitalize the first letter in Excel. By mastering this function, you’ll ensure your data is not only consistently formatted but also presents a polished and refined appearance, reflecting the high standards of your work.
This Tutorial Covers:
- Capitalize First Letter Using Formula
- Capitalize the First Letter of Each Word
- Capitalize Only the First Letter of the First Word Only
- Capitalizing the first letter of the first word while keeping the rest of the sentence unchanged
- Capitalize the first letter of the first word while using lowercase for the other letters
1. Capitalize First Letter Using the Formula:
Many helpful functions in Excel can be used to capitalize first letter in cells. Your data and the formula that uses it, however, cannot both be in the same cell. As a result, you must make an assistance column and place the formulas in it anywhere in your spreadsheet. You will be able to change formulas with their values once the computations are complete.
There are two situations in which you should capitalize:
- The first letter of each word
- Only the first letter of the first word
- Capitalize the First Letter of Each Word:
This one is quite simple to do because Excel has a feature just for it.
It is the PROPER function‘s job to capitalize each word’s initial letter.
Let’s say you want to fast change the first letter of each word to upper case and you have the dataset as shown below.
Also Read: How to Wrap Text in Excel (including shortcuts)
How to capitalize first letter of each word excel is shown below:
Step 1: Put the following formula in Cell B2.
=PROPER(A2)
Step 2: Fill the rest cell by the autofill handle. The result looks like the below:
By doing so, the first letter of each word in the cell being referenced would be capitalized.
It’s all simple!
Once you’ve achieved the desired outcome, you may copy the formula-containing cells and paste them as values to delink them from one another.
- Capitalize Only the First Letter of the First Word:
This one is a little trickier than the last one because Excel doesn’t have a built-in formula for capitalizing only the initial letter of the first word.
Nevertheless, you can still accomplish this (quickly) by combining a few formulas.
Again, there are two circumstances in which you could want to do this:
- Leave everything alone except for capitalizing the first letter of the first word.
- The first word’s first letter should be capitalized, while the rest should be written in lowercase (as there may be some upper-case letters already)
For each of these scenarios, other formulas would be utilized.
- Capitalizing the first letter of the first word while keeping the rest of the sentence unchanged:
Let’s say you just want to capitalize the first letter in the dataset below (and leave the rest as is).
How to capitalize first letter in excel is shown below:
Step 1: Put the following formula in Cell B2.
=UPPER(LEFT(A2,1))&RIGHT(A2,LEN(A2)-1)
Step 2: Fill the rest cell by the autofill handle. The result looks like the below:
The string’s first character in the cell is extracted using the LEFT function in the formula above. The initial letter is then changed to upper case using the UPPER function. The remainder of the string is then concatenated (which is extracted using the RIGHT function).
So. If any words already had alphabets in capital letters, they would not be altered. All subsequent letters would be lowercase.
- Capitalize the first letter of the first word while using lowercase for the other letters:
Another situation might be where you want to maintain everything in lowercase and only change the case of the first letter of the first word. This may happen when you text and want to change the case to a sentence case.
You might encounter cells in this situation where the remaining text is not already lower case, in which case you will need to compel the text’s conversion to lower case before using a formula to capitalize the first letter.
Assume you have the following dataset:
Also Read: How to Remove Line Breaks (Carriage Return) in Excel?
How to Capitalize the first letter of the first word while using lower case for the other letters in excel is shown below:
Step 1: Put the following formula in Cell B2.
=REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1)))
Step 2: Fill the rest cell by the autofill handle. The result looks like the below:
Explanation of how this formula works:
LOWER(A2) – This changes the text’s case throughout.
UPPER(LEFT(A2,1) – This changes the first letter of the text string in the cell to upper case.
REPLACE function: When using the REPLACE function, only the initial character is replaced with the uppercase equivalent.
Keeping the produced data dynamic is one advantage of utilizing a formula. For instance, the resulting data would immediately update if the formula was in place and any changes were made to the data in column A (the original text data). Make sure to convert the formula to values if you simply want to maintain the result and don’t want the original data.
2. Capitalize First Letter Using VBA:
Although using formulas to edit text data is a simple method, it does require a few more steps to obtain the result in a different column and then copy and paste it as values.
Consider employing a VBA code if you frequently need to alter the data as in one of the examples. You just need to set a VBA macro code once before you can add it to the Quick Access Toolbar.
Now, all you must do to capitalize the first letter the next time is choose the dataset and press the macro button in the QAT.
Let me now provide you with the VBA codes.
Capitalizing the initial letter of the first word in the code below will leave the rest of the text unchanged:
Sub CapitalizeFirstLetter()
Dim Sel As Range
Set Sel = Selection
For Each cell In Sel
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) – 1)
Next cell
End Sub
Additionally, the code that will capitalize the first letter of the text and lowercase the rest is provided below:
Sub CapitalizeFirstLetter()
Dim Sel As Range
Set Sel = Application.InputBox(“Select the Range (Excluding headers)”, “Range Selection”, Type:=8)
For Each cell In Sel
cell.Value = Application.WorksheetFunction.Replace(LCase(cell.Value), 1, 1, UCase(Left(cell.Value, 1)))
Next cell
End Sub
This VBA code must be entered into a standard module in the VB Editor.
Where should the VBA code go?
This code must be put in a standard module in Excel’s VB editor.
The steps to launching the VB Editor, add a module, and enter the code are as follows:
Step 1: Copy the VBA code above. Activate the “Developer” tab. Simply select “Visual Basic”.
Step 2: Right-click on any of the workbook items in the VB Editor. Select Module by clicking on Insert.
Step 3: Paste the VBA code mentioned above as per your situation in the module.
Step 4: After entering the code in the VB Editor, use the VB Editor to launch a macro (by clicking on the green play button in VB Editor toolbar). It will ask for Range Selection.
Select the range of your dataset except header.
The result looks like below.
The first letter in Excel cells can be capitalized using a few different techniques. You can select either the formula way or the VBA method depending on the scenario.
You may be interested:
