How to capitalize first letter in Excel cells

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.

In one of our previous articles we discussed how PROPER function can save the day. But since it capitalizes every word in a cell and lowers other letters, sometimes it can't be a cure-all.

Let's see what other options we have on the example of a shortlist of villains I like the most.

Capitalize first letter using formulas

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?

First letter Capital, lower the rest

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:
Insert an additional column to use the formula

Put the cursor into new B2 cell and input the following formula there:

=REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))

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.

Let me explain what the formula above means:

  • UPPER(LEFT(C2,1)) converts the first letter of C2 cell to capital.
  • REPLACE function is used to make sure that the whole text is returned with one specified letter changed - the first one in our case.
  • Adding LOWER(C2) as the first argument of the REPLACE function allows us to lower all other letters:
    Capitalize using REPLACE and LOWER functions together

Thus, you get properly looking cells written as sentences.

First letter Capital, disregard the rest

In 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.

But first, again, make sure to create another column to use the formula. Then, enter the following into B2:

=REPLACE(C2,1,1,UPPER(LEFT(C2,1)))

See, we deleted that "LOWER" part from the beginning of the formula. This little change won't lower all the letters in a cell but will still capitalize the first one:
Use REPLACE function only to avoid changing other letters

Tip. Don't forget to copy the formula down if Excel hasn't done it automatically.

Capitalize first letter using Text Toolkit: Change Case

If you decide that you need a quicker and faster way of making first letters in Excel cells capital, you will choose wisely!

Our Change Case from Text Toolkit will look over those pretty little letters of yours. It is available in the collection of 70+ toos for Excel - Ultimate Suite:

  1. Download and install Ultimate Suite collection to your PC.
  2. Run Excel and click the Change Case tool icon in the Text group under the Ablebits Data tab:
    Where to find Change Case in Excel

    The add-in pane will appear on the left side of your Excel window.

  3. 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.

  4. Choose the Sentence case option to make the first letter of each cell capital:
    Sentence case option to capitalize first characters in cells

    Note. If you want to save a copy of your data just in case, tick off the Back up the worksheet option before making any changes.

  5. Click the Change case button and see the result:
    See the result right in your table

Note. When every word in a cell (except the first one) starts with a capital letter, the add-in will not only capitalize the first character, but also lower the rest.

As you can see, capitalizing letters in Excel is not rocket science. Now you can do it in a couple of mouse-clicks and enjoy the results. Feel free to leave comments and ask questions down below :)

Table of contents

33 comments

  1. This formula is fantastic, saved me hours! Thanks!

  2. =PROPER(C5) GIVES Verticalscope Inc.
    C5 CONTENTS VERTICALSCOPE INC.

  3. It worked well

    Thank for your suggestion

  4. I am loving this website. Could you share the formula for capitalizing each first word in every sentence? I tried the following approach:

    =REPLACE(LOWER(A1),1,1,UPPER(LEFT(A1,1)))

    It capitalizes the first sentence only.

    • Use Proper.

      It will see the words as proper now and make every first word capital

  5. I downloaded from here the Ablebits suites for excel and it works perfectly.
    Thanks, I am now following you for more excellent tools.
    Regards

  6. Hey! Thanks for this, was very usefull.
    I have just one cuestion: ¿What about thar achivement of "1,1"?
    I understand why you use the number 1 for this "(LEFT(C2,1)", but, for this no ... "(LOWER(C2),1,1"
    Regards from Argentina!
    Ignacio.

  7. I have below text in single cell.

    commercial bank of qatar
    doha bank

    I need output like below in single cell.

    Commercial bank of qatar
    Doha bank

    • Hello!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  8. Currently trying to extract the last 3 letters from a specified cell, with only the first letter from those three being capitalized.
    The formula I am currently using is: =UPPER(RIGHT(B4,3))

    This gets me the last three letters from a specified cell, "NES", how do I edit the formula so that only the letter "N" is capitalized while "ES" is not capitalized??

  9. Thank you very much.
    The ultimate suit is really awesome, makes the work really easy.

  10. 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)

  11. Sir I need ur help in the case of how to adjust or make the first letter capital always when I'm writing any word on my excel. Tank u

  12. Hi Alexander,
    Thank you so much for your formula below is exactly what I am looking for!!
    =PROPER(REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1))))

    Greatly appreciated.

  13. (Thank you!) & (Bless you!) = Exactly what I am thinking right now
    // i was just having hard times importing some book titles
    Thanks again!

    • can you tell me which formula u did use for the casing

  14. 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 (https://www.ablebits.com/downloads/index.php). Perhaps you just need an update. If you have any more questions, I will be pleased to answer them.

  15. Hi I am using your formula =REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))
    how to I do it if the cell has more than one word
    i.e my cell has MR A G SMITH however I wish to change to to Mr A G Smith
    the formula above converts it to Mr a g smith
    thank you in advance

      • Very helpful, thank you

      • Really appreciated Mr Alex, but whats the formula if i wish the have JOHN Doe Smith in a cell?

  16. Sorry but Tyler Durden is not a villain, great otherwise.

  17. Thank you for the help.
    God Bless You! :)

  18. Thanks

  19. i need a small software for a manufacturing company can u help me.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)