How to capitalize the first letter in a cell in Excel

If you’d like to capitalize all letters in a cell, it’s easy – just use the following formula (assuming cell “A1″ has the text you want to capitalize).

=PROPER(A1)

But what if you just want to capitalize the first word in the cell “A1″? For example, you have the following  text in a cell “A1″ and you’d like to capitalize just the first letter:

“How to Get a High School Diploma in 1 Day”

To capitalize “How” in cell “A1″, use the following formula:

=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

The result:

“How to get a high school diploma in 1 day”

Not working for you?

Remember to replace “A1″ with the cell that contains the string of words you want to parse out.

This entry was posted in Functions. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

33 Comments

  1. raheel
    Posted October 23, 2014 at 7:42 am | Permalink

    nice post, feels happy and my problem solved thanks.

  2. Sarah
    Posted May 20, 2014 at 12:51 pm | Permalink

    Just made a big job disappear with this formula, brilliant

  3. ExpertLearner
    Posted April 15, 2014 at 10:40 am | Permalink

    Hi, thx. I have some capitalized phrases and some all caps, so, I used this:

    =CONCAT(UPPER(LEFT(A1;1));RIGHT(LOWER(A1);LEN(A1)-1))

    Nice.

  4. Arnaldur
    Posted November 8, 2013 at 10:42 am | Permalink

    Thank you! You just made my day!

    • karen
      Posted November 8, 2013 at 10:58 am | Permalink

      Hi Arnaldur! I am so happy I was able to help :)

  5. Posted October 9, 2013 at 3:03 am | Permalink

    Hi,

    I have used “=PROPER()” formula, but it doesnt help me. I dont want my employees to have to copy and paste corrected text. I need Excel to correct the same text my employees fill in. And i need it to be corrected in the same cell as it was entered.

    For example: Cell A1 – text entered: i need a car ->hit enter-> Cell A1 corrected: I Need A Car

    • karen
      Posted October 9, 2013 at 9:49 am | Permalink

      Hi Dusan! Unfortunately, you can’t achieve that using the =PROPER() formula. If you want programmatically capitalize all the text in one go, you can use a macro. I have a tutorial here: http://howtouseexcel.net/how-to-capitalize-all-words-using-an-excel-macro-instead-of-the-proper-function However, you still have to run the macro. It doesn’t automatically update the cell immediately after you enter the data.

      • Dusan
        Posted October 21, 2013 at 2:42 am | Permalink

        Thank you :)

        In the end I used this:

        Private Sub Worksheet_Change(ByVal Target As Range)

        ””””””””””””””””””””””
        ‘Forces text to Proper case for the range A1:B20
        ””””””””””””””””””””””

        If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
        On Error Resume Next
        If Not Intersect(Target, Range(“A1:B20″)) Is Nothing Then
        Application.EnableEvents = False
        Target = StrConv(Target, vbProperCase)
        Application.EnableEvents = True
        End If
        On Error GoTo 0

        End Sub

        It was found here: http://www.ozgrid.com/VBA/force-case-text.htm

        • karen
          Posted November 8, 2013 at 10:58 am | Permalink

          Hi Dusan! Thanks for sharing this! I’m glad you found a solution that worked!

    • Prabhat
      Posted February 24, 2014 at 3:21 am | Permalink

      thank , done

      • karen
        Posted April 7, 2014 at 4:47 pm | Permalink

        You’re welcome Prabhat!

  6. Urmez
    Posted September 29, 2013 at 9:45 pm | Permalink

    Try This

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

  7. Tik.
    Posted September 26, 2013 at 10:16 am | Permalink

    Thank you for taking the time to share how to capitalize letters in excel. Thank you.

    • karen
      Posted September 27, 2013 at 11:13 am | Permalink

      You’re welcome! Glad to hear it was helpful for you :)

      • Bilal Jilani
        Posted September 30, 2013 at 5:23 pm | Permalink

        I have a problem which i can’t seem to figure out, would appreciate it if you could come up with a solution.
        A cell contains the last name of customers (uppercase) and in the same cell the last name is followed by the address.
        e.g ” SMITH A, 36 James street”
        I want to know how to get the last name of a customer into another cell without having to manually do it everyday for 10.000 customers.
        The surname changes as you scroll down and each surname is only written once, so if there was another SMITH there would be a cell bellow which will one contain the first INITIAL of the first name e.g ” J, 178 Brooklyn Street.

  8. max
    Posted July 23, 2013 at 12:51 am | Permalink

    this just only for a cell.
    how can we make the whole column do the same thing?

    • karen
      Posted July 23, 2013 at 8:52 am | Permalink

      Hi Max, you can easily just drag down the formula using Excel’s auto-fill function. When you move the cursor to the bottom right hand corner of the cell, you’ll see a small square. When you double click on it, your formula automatically gets copied down to the last cell.

  9. Melody
    Posted April 20, 2013 at 12:28 am | Permalink

    Hi there,

    I get people to enter data for me and they don’t always use capitals. Is there any way I can make it automatically capitalize certain columns when they are typing?

    • karen
      Posted June 20, 2013 at 11:54 am | Permalink

      Hi Melody, I understand that you want the first letter of the cell to always be capitalized as they are typing. Do you want to capitalize just the first letter of the cell? Or the first letters of all the words in the cell? Here’s what you can do:

      1. Ask your employees to enter data under column A.

      2. In cell B1, enter either “=PROPER(A1)” or “=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))” depending on whether you want to capitalize just the first letter of the cell Or the first letters of all the words in the cell.

      3. Drag the formula in B1 all the way down the Excel sheet so that it gets copied all the way down.

      After your employees submit the Excel sheet to you, just copy and paste column B as values and then delete column A. Hope this helps!

  10. Peter Wharton
    Posted January 16, 2013 at 6:24 am | Permalink

    Can anyone help I have used the formula to capatalise first letters in cells.
    =CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

    However I need to also capitalise the first letters after full stops i.e.
    “I went to the park. it was a lovely day.” to actually read
    “I went to the park. It was a lovely day.”

    Is there a formula for this.

    • karen
      Posted January 16, 2013 at 2:35 pm | Permalink

      Peter, I don’t have one formula in mind. But I have some thoughts on how you can get this done. Here are the steps:

      Step 1. Separate the cell into several cells, with one cell containing one sentence each. You can do this by using the “Text to columns” feature using the ff. steps:

      – Select the range of data that you want to convert.
      – On the Data tab, in the Data Tools group, click Text to Columns.
      – In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
      – In Step 2, select the Other check box, enter period “.” in the blank next to “Other”, and then clear the other check boxes under Delimiters.
      – The Data preview box shows each sentence in one column.
      – Click Finish.

      Step 2. Use the formula in this tutorial to capitalize the first letters.

      Step 3. Combine the cells again into one cell by using the CONCATENATE function.

      My suggestion seems a little convoluted, so I apologize. But this is the only thing I can think of right now. If you find a better solution, let me know!

  11. Drew
    Posted December 19, 2012 at 4:37 pm | Permalink

    Hi Karen, what if I have a list of cells with various phrases in them and only some of the first words are capitalized, and I want the end result to be that, for every cell, each phrase or sentence should have only the first letter of the first word capitalized, that is, to make each cell grammatically correct in terms for capitalization.

    CELL A1: ‘the warehouse is at 85 percent capacity’ turns into ‘The warehouse is at 85 percent capacity’

    CELL A2: ‘The Warehouse is at 90 Percent Capacity’ into ‘The warehouse is at 90 percent capacity’

    • karen
      Posted December 21, 2012 at 1:55 am | Permalink

      Hi Drew, did you try this formula?

      =CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

      This should hopefully work.

  12. Arnfinn
    Posted December 4, 2012 at 2:54 pm | Permalink

    Nice “trick”. Thanks a lot.

    • karen
      Posted December 4, 2012 at 3:05 pm | Permalink

      Happy to hear that you found this “trick” helpful!

      • LX
        Posted October 15, 2013 at 11:49 pm | Permalink

        My friend, i’ve tried this formulas but it doesnt work for me

        • karen
          Posted October 17, 2013 at 1:33 pm | Permalink

          I am sorry to hear that the formula did not work for you. Are you sure that you have the correct cell reference in your formula? For example, the original text must be in cell A1 for the exact formulas above to work.

  13. Posted September 26, 2012 at 2:10 pm | Permalink

    Thanks! Was literally able to cut-paste with success. Saved me some time learning excel macros!

  14. Sipho
    Posted August 23, 2012 at 2:40 am | Permalink

    that formula is magic ! There are so many complicated solutions on the net & this “=CONCATENATE(UPPER(LEFT(F2,1)),RIGHT(F2,LEN(F2)-1))” just worked beautifully, thank you !

    • karen
      Posted August 23, 2012 at 7:21 am | Permalink

      Glad you found it helpful :)

  15. Stephanie
    Posted December 2, 2011 at 2:16 pm | Permalink

    How to capitalize the first letters in all the words in a cell in Excel? Per example how to make: james hasdes —-> James Hasdes

    • karen
      Posted January 25, 2012 at 3:28 pm | Permalink

      Stephanie, that’s very easy. Just use the “PROPER” formula. For example, if you want to capitalize the first letters of all the words in cell A1, just type “=PROPER(A1)”. Good luck!

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>