Skip to content How to Capitalize a Text String’s First Letter in Excel (using Formula & VBA)
Many individuals use Excel with text data in addition to using it with numeric data. It may be as straightforward as keeping a list of names or it might be more complicated.
One frequent duty when working with text data is to ensure consistency by capitalizing the initial letter in each cell (or to capitalize the first letter of each word in all the cells)
I’ll demonstrate a few ways to capitalize the initial letter in Excel cells in this lesson.
let’s start
This instruction explains: 1. First Letter Capitalization Using Formula 1.1 Initial Letter of Every Word 1.2 Only the first letter of the word 2. Using VBA, capitalize the first letter
1. First Letter Capitalization Using Formula
There are two situations in which you should capitalize:
- Each word’s first letter
- The only letter in the first word is
1.1 Initial Letter of Every 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 initial letter of each word to upper case and you have the dataset as shown below.
The formula you may use is as follows:
=PROPER(A2)
By doing so, the initial letter of each word in the cell being referred to would be capitalized.
Simple to understand!
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.
1.2 Only the first letter of the word
This one is a little trickier than the last one since 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 formulae.
Again, there are two circumstances in which you could wish 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 remainder should be written in lower case (as there may be some upper case letters already)
For each of these scenarios, other formulae would be utilized.
See how we can do this!
Leave everything alone and capitalize the first letter of the first word.
Let’s say you just want to uppercase the first letter in the dataset below (and leave the rest as is).
The equation to accomplish this is given below:
=UPPER(LEFT(A2,1))&RIGHT(A2,LEN(A2)-1)
The first character of the string 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 may be when you want to maintain everything in lowercase and simply alter the case of the first letter of the first word. This may happen when you text and wish to change the case to a sentence case.
You could encounter cells in this situation where the remaining text is not already lowercase, in which case you will need to compel the text’s conversion to lowercase before using a formula to capitalize the initial letter.
Assume you have the following dataset:
The following formula will uppercase the initial letter of the first word and lowercase the rest:
=REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1)))
I’ll explain how this formula functions.
LOWER(A2) changes all of the text to lowercase.
The initial letter of the text string in the cell is changed to upper case by typing UPPER(LEFT(A2,1).
Only the initial character is replaced with the upper case form of it when the REPLACE function is invoked.
Keeping the produced data dynamic is one advantage of utilizing a formula. For instance, the resultant 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 careful to convert the formula to values if you simply want to maintain the result and don't want the original data.
2. Using VBA, capitalize the first letter
Although using formulae to edit text data is a simple method, it does need 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 like in one of the aforementioned examples. You just need to set a VBA macro code once before you can add it to the Quick Access Toolbar.
Now, all you have to do to capitalize the first letter the next time is choose the dataset and press the macro button in the QAT.
Even better, you can construct an add-in and use VBA code throughout all of your workbooks (and can even share these with your colleagues).
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 The code to uppercase the first letter of the text and lowercase the rest is shown below:
Sub CapitalizeFirstLetter()
Dim Sel As Range
Set Sel = Selection
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.
The initial letter in Excel cells may be capitalized using a few different techniques. You may select either the formula way or the VBA method depending on the circumstance.
I hope this Excel tutorial was helpful.
