The Microsoft Office suite has some pretty unique features. Microsoft Word, PowerPoint, all of them have their individual specialties.
One common feature is capitalizing words. In word or PowerPoint alike, there is a button on the Home ribbon that lets users capitalize the first letter, each word, each letter, and also make the letters lowercase.
However, Microsoft Excel has no such button but one of the most frequent tasks in data cleaning is capitalizing first letters or words in excel.
There are quite a few instances where you might require to capitalize words or letters.
So, let’s look at them one by one and learn how to capitalize the first letter in excel.
How to Capitalize First Letter in Excel?
Scenario1# Capitalizing the first letter of a single word
Suppose, you have a list of first and last names. You just need to capitalize the first letter of each name.
You can use the PROPER function in this case. Type the following formula to capitalize the first letter in excel:
The PROPER function capitalizes the first word of the text you select.
Scenario2# Capitalizing every letter of a single word
Now, when you need to capitalize every letter of a single word, you can use the UPPER function.
Type the following formula to capitalize every letter in excel:
The UPPER function converts a text into uppercase letters.
Scenario3# Capitalizing the first word in a sentence
If you want to capitalize just the first word in a sentence you can use the LOWER, REPLACE, UPPER, and LEFT functions in combination.
Type the following formula to capitalize the first word in a sentence in excel:
To understand the formula let’s look at the syntax of the REPLACE formula first.
=REPLACE (old_text, start_num, num_chars, new_text)
The old_text refers to the text you want to replace. We want to replace the lower case of the text, so we have used the LOWER function.
Start_num refers to the letter (first, second, third, etc.) from which we want to start replacing and the argument takes numbers 1,2,3.
Num_chars refers to the number of characters (1,2,3, etc.) we want to replace from the starting number.
Until now the formula we have used tells excel to turn the text’s first letter into lowercase.
Now, it’s time to turn the lowercase letter into uppercase. We can do that by the UPPER function.
So, in the new_text argument, we have entered the UPPER function. However, converts a text into uppercase letters but we just need one letter to become uppercase.
Now, we have used the LEFT function. The LEFT function returns a specified number of letters from the left of the text string you select.
So, if we specify the first letter in LEFT, the UPPER function will make it uppercase.
Scenario4# Capitalizing each word in a sentence except for prepositions and principal verbs
Now, not always we want to capitalize each word or each letter. There might be some letters we want lowercase and some uppercase in a sentence. Usually, prepositions and principal verbs are left lowercase in a sentence.
It is possible to do that in excel but requires a bit of tweaking.
In these sentences, I will capitalize each word except for the words- will, be, on, with, and in.
We can do it by using the PROPER and SUBSTITUTE functions. Type the following function:
The formula seems very complex at first but let me break it down.
First, you have to use one substitute for each word you want to keep lowercase. In this case, I chose 5 words that I don’t want to be in uppercase, so I used five SUBSTITUTE functions.
Now, use the PROPER function to capitalize each word in the sentence, and then we have to utilize the SUBSTITUTE functions to lowercase the words we have chosen.
To do that, you can make a list of common verbs and prepositions, or if you have a list on your mind type it in a column.
Keep in mind that every first letter of the words in the list should be uppercase.
Now, after using the PROPER function, for old_text use the first cell in the list, in this case, H5 and for new_text type, the lower case version of the selected cell, in this case, “will”.
Close the bracket and repeat the process for each cell in the list and when you have completed the list, you will see a black bracket when you close the last bracket in the formula. This marks the end of the formula.
Press enter and drag the fill handle down to copy the formula for all the sentences.
I have tried to create scenarios in which you need to capitalize words or letters. You can always use the learnings here to tweak the formulas and use different capitalizing techniques.
So, there you go, you know how to capitalize the first letter in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.