Excel First Letter Capital
Lillia Iniguez
The above formula uses the LEFT function to extract the first character from the string in the cell. It then uses the UPPER function to change the case of the first letter to upper. It then concatenates the rest of the string (which is extracted using the RIGHT function).
Another scenario could be where you want to change the case of only the first letter of the first word and keep everything in lower case. This could be when you text that you want to convert to sentence case.
In this scenario, you may get some cells where the remaining text is not in the lower case already, so you will have to force the text to be converted to lower case, and then use a formula to capitalize the first letter.
If you often need to use change the data as shown in one of the examples above, you can also consider using a VBA code. With a VBA macro code, you just have to set it once and then you can add it to the Quick Access Toolbar.
How can we change the case of the first letter from lower to upper in Excel cells? Do we have to type each character manually into every cell? Not anymore! Today I will share three methods of capitalizing first letters in your table.
I believe when it comes to text in Excel, one of the most commonly required tasks is to capitalize first letters in the cells. Whenever you have lists of names, products, tasks, or anything else, you will for sure have some of them (if not all) written only in small or capital letters.
Excel has lots of useful functions suitable for capitalizing first letter in the cells. However, you can't have both, your data and formula that references it, in one cell. Thus, you need to create a helper column somewhere in your worksheet in order to place the formulas there. When it's done, and calculations are made, you'll be able to replace formulas with their values. Shall we begin?
To make only the very first letter capital in Excel cell and lower the rest at the same time, start with inserting an additional column for the results. In my example it is column B. Right-click the column name (B) and choose Insert from the context menu. The column is inserted between A and C columns, and you can change its header name if there is one:
Tip. It is most likely that the rest of the rows will be populated with the adjusted formula automatically. Otherwise, you can quickly copy the formula down the column by drag-n-dropping or double-clicking that small square in the lower-right corner of the cell with a formula.
Thus, you get properly looking cells written as sentences.
First letter Capital, disregard the restIn order to capitalize the first letter of the cell and leave the other characters as they are, we will use the same formula as above with a little modification.
The add-in pane will appear on the left side of your Excel window.
- Manually select the range of cells where you want to change case, B2:B10 in our case.Tip. You can select the range before running the tool. It will show the selected range in the corresponding field automatically.
Hello!
I don't think it's possible to do this with a regular Excel formula. We have a tool that can solve your task in a couple of clicks - Change Case tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi Sir im trying to use the sentence case but that dont give what i require for example (Lecture Notes in Physics: Chiral Soliton Models for Baryons (Hardcover) )im using sentence formula and i chnages these sentence as Lecture notes in physics: chiral soliton models for baryons (hardcover)
Hi Saleem,
Please have a look at this part of the article above: First letter Capital, lower the rest
If you still have difficulties, please provide the exact formula you're using that doesn't work for you.
Sir, I need to convert multiple sentences into sentence case. Ablebits is able to do that, but one problem: if I had intentionally capitalized the first letter of a word (for example, a proper noun such as my name Selvaraj), Ablebits converts its Upper case into lower case. Selvaraj becomes selvaraj. This is not right. I just don't what the tool to disturb any letter that I have intentionally made caps. It should only change the first letter of the sentence into caps and leave everything else undisturbed. How to resolve this? Thank you.
One more: When you use the Ablebits, it backs up the original sheet. I don't want that. How to solve it? Thank you.
Sir, I tried using sentence case in my file and made sure that your name had remained written correctly - i.e. Selvaraj. Only the first letter of a sentence is transformed into upper case. Other letters do not change. Please see what version of Ablebits Tools you are using and compare it with the current one ( ). Perhaps you just need an update. If you have any more questions, I will be pleased to answer them.
One of the most important skills to learn with Excel formulas is the concept of nesting. Put simply, nesting just means putting one function inside another. Nesting is super useful, but it does take some practice. You have to learn to read a formula from the inside out. The formulas below are good examples of nesting. Practice reading the formulas starting with the innermost functions.
In this example, the goal is to capitalize the first letter in a text string with a formula in Excel. This involves a bit of creative thinking because Excel does not offer a built-in function to capitalize only the first letter in a text string, unlike many other languages. This article explains a few approaches to the problem, including the formula featured in the worksheet above.
The simplest way to capitalize the first letter in a text string is to use the PROPER function, which is designed to capitalize words. For example, if we give PROPER the word "apple", PROPER returns "Apple":
This works very nicely for a single word or a person's name. However, it won't work in the worksheet shown because PROPER will capitalize all words. For example, If we give PROPER the text string "an apple a day", we get back "An Apple A Day":
Another solution to this problem is to take a more literal approach: extract the first letter in the text string, capitalize it, and then concatenate the result to the remaining characters. This can be done with the formula below, which is based on four separate functions - LEFT, UPPER, MID, and LEN:
This is an example of nesting functions in Excel. Notice that the LEFT function is nested inside UPPER, and the LEN function is nested inside the MID function. The way to read nested functions is from the inside out:
This formula works fine for the problem as stated: it will capitalize the first letter of the sentence and leave all remaining characters unchanged. But do we need four functions to perform this task? Can't we do better?
The REPLACE function is designed to replace one or more characters in a text string specified by location with another text string. For example, we can replace the last 3 letters in "ABCDEF" with "XYZ" like this:
In other words, REPLACE swaps three characters starting at character 4 with "XYZ". We can use the REPLACE function to solve this problem by replacing the first letter with a capitalized version of itself. This is the approach seen in the worksheet shown, where the formula in cell D5 is:
Compared to the previous formula, this formula only needs three functions and doesn't require concatenation at all. The REPLACE does the work of replacing the first letter in place, leaving all remaining characters unaffected.
Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.
I could use text to column tool and separate the first letter and the rest of letters. And I convert the first split into upper case, then the second split into lower case. Then combine them together. However, I am wondering if there is simpler way to convert the fist letter into Upper case at once.
I have a very large sheet where some of the text was written in upper case. I know the UPPER and LOWER functions will change the entire sentence to lowercase, but is there a PROPER function (like in excel) to capitalize the first letter of the sentence with the subsequent letters in lower case?
I just came across a need for this. In my case, it was cleaning up first name and last name in order to join them together in a new field for inclusion on a generated PDF certificate. These fields are user-input from a form, so we get all kinds of messes in there - all lower, all upper, and stray caps. The below formula converts each name to upper, collects the first letter, then converts the name to lower case and collects everything after the first letter (up to 30 more characters in each name.)
Hello, I'd like the +6 Days column to show the date in the manual adjustment column if the manual adjustment column has a date. If the manual adjustment column is blank, I'd like the +6 Days column to show the start date plus 6 days. Any ideas on how to adjust the formula below? Thanks! Lindsay
1. If you want all the words first letter should capitalize then= Transform data>Goto Power query> Right click>Click on Transform>Will get these Screenshot options you can capitalize each word.
2. If you want to capitalize only one intial word in the full sentence then split the column by number of Character> once you seperate the column then capitalizethe 1st column then merge both the column.
Hi Mariusz, thanks for the fast reply. I confirm it works to split the column on the first space delimter, use Text.Proper on that first column only, and then merge the two columns back together again. I was a little surprised this cant be done in one line, like when formatting to Text.Proper. However, its simple enough, which I like. In reality I have a column that is populated with values separated by a comma from a 'select multiple' question in a survey. Some rows have one value, some two, some three. Eg.
d3342ee215